Archive

Archive for July, 2012

“Identity Gap” Issue with the new SEQUENCE feature in SQL Server 2012 … and its workaround

July 27, 2012 3 comments

In my previous post I discussed about an issue with IDENTITY property. Here today while working on a similar new feature “SEQUENCE”, I found a similar kind of behavior with it.

Here also when you restart SQL Server or restart the service the last sequence number jumps to a higher random number.

Here is a simple code to reproduce this issue:

-- CREATE a simple Sequence
CREATE SEQUENCE CountBy1 
	START WITH 1 
	INCREMENT BY 1 
	MINVALUE 0 
	NO MAXVALUE ;
GO


-- CREATE a test table:
CREATE TABLE TEST_SEQ (ID INT, NAME VARCHAR(200));

-- INSERT some records:
INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Manoj Pandey'

INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Gaurav Pandey'
GO

-- Check the INSERTed records before server restart:
SELECT * FROM TEST_SEQ
GO


-- RESTART SQL Server & INSERT a new record:
INSERT INTO TEST_SEQ
SELECT NEXT VALUE FOR CountBy1, 'Garvit Pandey'
GO

-- Check the INSERTed records after server restart:
SELECT * FROM TEST_SEQ
GO


--// Final cleanup
DROP TABLE TEST_SEQ
DROP SEQUENCE CountBy1

Finally I got the following output:

As you can see by running the above test before I restarted SQL Server the SEQUENCE value assigned to the last record was 2, but when I restarted the new SEQUENCE value generated is 51.

Reason: Actually while creating SEQUENCE object SQL Server engine caches the new SEQUENCE values to Increase performance. By default the cache size is 50, so it caches values upto 50 values, and when SQL Server restarts it starts after 50, that’s a bug.

Workaround: To avoid this situation you can put an “NO CACHE” option while declaring the SEQUENCE object, like:

-- CREATE a simple Sequence
CREATE SEQUENCE CountBy1 
	START WITH 1 
	INCREMENT BY 1 
	MINVALUE 0 
	NO MAXVALUE 
	NO CACHE ; -- here
GO

This will not cache the future values and you wont get this issue of jumping values and gaps.

To know more about SEQUENCES check my previous blog post, [link].

Jump/Gap Issue with IDENTITY property in SQL Server 2012 … and its workaround (not a bug)

July 27, 2012 5 comments

Sometime back there was discussion going on in an SQL Server forum regarding issues with IDENTITY property in the new SQL Server 2012. The issue was that, when restarting SQL Server (or service) the IDENTITY value in a particular table having IDENTITY column jumps to a higher random number and starts from there.

I tried to reproduce this issue, the SQL Server version I’m using is as follows:

Microsoft SQL Server 2012 RC0 - 11.0.1750.32 (X64) 
	Nov  4 2011 17:54:22 
	Copyright (c) Microsoft Corporation
	Business Intelligence Edition (64-bit) on Windows NT 6.1  
	(Build 7601: Service Pack 1)

I reproduced the issue with following code:

-- CREATE a test table:
CREATE TABLE TEST_IDENTITY (
	ID INT IDENTITY(1,1), 
	NAME VARCHAR(200)
	);

-- INSERT some records:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Manoj Pandey'

INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Gaurav Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 2 records, with ID value 1, 2.

-- Check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
GO


-- RESTART SQL Server and check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '11', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- INSERT a new record:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Garvit Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 3 records, with ID value 1, 2, 12.
GO

--// Final cleanup
DROP TABLE TEST_IDENTITY


Finally I got the following output:

As you can see by running the above test before I restarted SQL Server the IDENTITY value was 2, but when I restarted the IDENTITY value changed to 11.
Thus the new records was inserted with value = 12.

The above bug/issue has been logged in the Microsoft Connect site, [here].

Workaround: Right now there is no fix for this, so you need to check all tables in you database every time your SQL Server restarts and reseed the correct IDENTITY value. Check my blog post to reseed the IDENTITY value.

Reorganize Index vs Rebuild Index in SQL Server

July 25, 2012 Leave a comment

Well-designed indexes on tables/views improves the performance of queries run against a database by reducing disk I/O operations and consume fewer system resources. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements.

SQL Server Database Engine automatically maintains indexes whenever INSERT, UPDATE, or DELETE operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered/fragmented in the database. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

SQL Server has provided ways to reduce/remedy fragmentation by Reorganizing or Rebuilding an Index.

1. Reorganize Index: uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

2. Rebuild Index: drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

So, which approach to go with, Reorganize or Rebuild?

First of all we’ll check the fragmentation status of a particular table or an index, by using sys.dm_db_index_physical_stats function.

Here we will check the status of all indexes in [HumanResources].[Employee] table of [AdventureWorks2012] database.

select 
	x.name as Index_Name,
	s.database_id, s.object_id, s.index_id, s.index_type_desc, -- General info columns
	s.avg_fragmentation_in_percent, s.fragment_count, s.avg_fragment_size_in_pages -- stats we need
from sys.indexes x
cross apply sys.dm_db_index_physical_stats (
	DB_ID(N'AdventureWorks2012'), -- database_id
	x.object_id, -- object_id
	x.index_id, -- index_id
	NULL, -- partition_number
	NULL) as s -- mode
where s.object_id = object_id('HumanResources.Employee')

Output:

We will use the following criteria setup by Microsoft to detirmine the best method to correct the fragmentation:

avg_fragmentation_in_percent value | Corrective statement
> 5% and <= 30%			     ALTER INDEX REORGANIZE
> 30%				     ALTER INDEX REBUILD WITH (ONLINE = ON)*

The above results shows that the Indexes [AK_Employee_LoginID] and [AK_Employee_NationalIDNumber] requires Rebuild and rest of them are good.

–> TO REBUILD:

--// To Rebuild [AK_Employee_LoginID] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_LoginID
	ON HumanResources.Employee
REBUILD;
GO

--// To Rebuild All indexes, use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

–> TO REORGANIZE:

--// To Reorganize [AK_Employee_NationalIDNumber] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_NationalIDNumber
	ON HumanResources.Employee
REORGANIZE;
GO

--// To Reorganize All indexes on [HumanResources].[Employee], use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REORGANIZE;
GO

So, check the fragmentation status by using the DM function sys.dm_db_index_physical_stats and then decide to do either REORGANIZE or a REBUILD on an Index.

Microsoft Assessment and Planning (MAP) Toolkit 7.0 now available | Accelerate your migration to the private cloud

July 12, 2012 Leave a comment

The Microsoft Assessment and Planning (MAP) Toolkit team is pleased to announce that MAP 7.0 is now available for download.

The Microsoft Assessment and Planning (MAP) Toolkit is an agentless, automated, multi-product planning and assessment tool for quicker and easier desktop, server and cloud migrations. MAP provides detailed readiness assessment reports and executive proposals with extensive hardware and software information, and actionable recommendations to help organizations accelerate their IT infrastructure planning process, and gather more detail on assets that reside within their current environment. MAP also provides server utilization data for Hyper-V server virtualization planning; identifying server placements, and performing virtualization candidate assessments, including ROI analysis for server consolidation with Hyper-V.

The latest version of the MAP Toolkit adds several new scenarios to help you plan your IT future while supporting your current business needs. Included scenarios help you to:
• Plan your deployment of Windows Server 2012 and Windows 8 with hardware and infrastructure readiness assessments
• Size your desktop virtualization needs for both Virtual Desktop Infrastructure (VDI) and session-based virtualization using Remote Desktop Services
• Ready your information platform for the cloud with SQL Server 2012
• Virtualize your existing Linux servers onto Hyper-V
• Identify opportunities to lower your virtualization costs with Hyper-V using the VMware migration assessment
• Assess user and device access for Lync 2010 Standard licensing

Key features and benefits of MAP 7.0 help you:
• Determine your readiness for Windows Server 2012 and Windows 8
• Migrate your VMware-based virtual machines to Hyper-V
• Size your server environment for desktop virtualization
• Simplify migration to SQL Server 2012
• Evaluate your licensing needs for Lync 2010
• Determine active users and devices

Assess Windows Server 2012 readiness
MAP 7.0 assesses the readiness of your IT infrastructure for a Windows Server 2012 deployment. This feature includes detailed and actionable recommendations indicating the machines that meet Windows Server 2012 system requirements and which may require hardware updates. A comprehensive inventory of servers, operating systems, workloads, devices, and server roles is included to help in your planning efforts.

Determine Windows 8 readiness
MAP 7.0 assesses the readiness of your IT environment for your Windows 8 deployment. This feature evaluates your existing hardware against the recommended system requirements for Windows 8. It provides recommendations detailing which machines meet the requirements and which machines may require hardware upgrades.
Key benefits include:
• Assessment report and summary proposal to help you to understand the scope and benefits of a Windows 8 deployment.
• Inventory of desktop computers, deployed operating systems, and applications.

Virtualize your Linux servers on Hyper-V
MAP 7.0 extends its server virtualization scenario to include Linux operating systems. Now, MAP enables you to gather performance data for Linux-based physical and virtual machines and use that information to perform virtualization and private cloud planning analysis for both Windows and Linux-based machines within the Microsoft Private Cloud Fast Track scenario.
Key features allow you to:
• Incorporate non-Windows machines into your virtualization planning.
• View consolidation guidance and validated configurations with preconfigured Microsoft Private Cloud Fast Track infrastructures, including computing power, network, and storage architectures.
• Get a quick analysis of server consolidation on Microsoft Private Cloud Fast Track infrastructures to help accelerate your planning of physical to virtual (P2V) migration to Microsoft Private Cloud Fast Track.
• Review recommended guidance and next steps using Microsoft Private Cloud Fast Track.

For a comprehensive list of features and benefits, click here.

Next steps:
Download MAP Toolkit 7.0.
• Send your comments to the MAP Team.
Learn more about MAP.

Get the latest tips from Microsoft Solution Accelerators—in 140 characters or less! Follow us on Twitter: @MSSolutionAccel.

Querying Excel 2010 from SQL Server in 64-bit environment

July 10, 2012 11 comments

In my previous post I discussed about querying Excel files with both 2003 & 2007 versions from SQL Server.

Here we will see how can we query Excel 2010 files. Will use the same scripts from my previous post to create Linked Server to point to the Excel source. We don’t need to do any change in the scripts for this version, but we will require some additional steps to do, that we’ll see later on.
 

Please Note: To use a Linked server for fetching records from a particular source first you have to enable the “Ad Hoc Distributed Queries” configuration setting. This is disabled by default, if not enabled you will see the following error message:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

 

–> So let’s first of all enable this:

USE [MSDB]
GO

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

You can also enable this setting graphically by going to “Surface Area Configuration” and enable it.
 

–> Now, to access the Excel file there are 2 ways:

1. Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other options

2. Indirectly by creating a Linked Server first of all, then:
2.a. fetching records from Excel by using OPENQUERY() function or
2.b. by using the Linked Server name within the SELECT query

-- 1. Directly, by using OPENROWSET() function
SELECT * FROM OPENROWSET (
	'Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx;HDR=YES;IMEX=1',
	'SELECT * FROM [Sheet1$]'
);

-- OR --

-- 2. Indirectly, by Creating Linked Server & using OPENQUERY:
EXEC sp_addLinkedServer
	@server= N'XLSX_2010',
	@srvproduct = N'Excel',
	@provider = N'Microsoft.ACE.OLEDB.12.0',
	@datasrc = N'E:\SQL Server - Blogs\LinkedServer2010.xlsx',
	@provstr = N'Excel 12.0; HDR=Yes';
GO

-- 2.a. Using OPENQUERY() function:
SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]')

-- 2.b. Using the Linked Server name within the SELECT query:
SELECT * FROM XLSX_2010...[Sheet1$]

Neither of the above options runs and ends up in following error:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “XLSX_2010”.

 

–> I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider:

USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

The previous error was gone, but this new error surfaced:

Msg 7438, Level 16, State 1, Line 1
The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0” cannot be loaded in-process on a 64-bit SQL Server.

To resolve this error you need to get the “Access Database Engine 2010” to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010” (*.mdb and *.accdb) files and “Microsoft Office Excel 2010” (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.

–> Get the Microsoft Access Database Engine 2010 Redistributable [http://www.microsoft.com/en-us/download/details.aspx?id=13255]
There will be two files:
1. AccessDatabaseEngine i.e. 32-bit and
2. AccessDatabaseEngine_x64 i.e. 64-bit
My machine is 64 bit and the SQL Server is also 64 bit, but let’s just check it:

select @@VERSION
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   
	Apr 22 2011 19:23:43   
	Copyright (c) Microsoft Corporation  
	Data Center Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

So, download and install the second “AccessDatabaseEngine_x64.exe” file.

While installing the 64-bit EXE it gives following error:

You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2010, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Microsoft Access Database Engine 2010:
Microsoft Office Professional Plus 2010, Microsoft Office Visio 2010

So, this error tells that the MS Office product installed in my system is 32-bit, that I was not aware of.
To get this resolved you need to have everything in 64-bit environment. So, I will uninstall the 32-bit version of MS Office and re-install MS Office with 64-bit version.
 

Now if you again run the above queries, you will see them running successfully and pulling records from Excel file.

–> So, what additional steps we did for Excel 2010 to work?

Step 1:
Registered the Microsoft.ACE.OLEDB.12.0 provider for AllowInProcess & DynamicParameters properties.
This option registeres the provider and both the properties in registry. You can check what system stored procedure [sp_MSset_oledb_prop] does with sp_helptext SP.
Like:

EXEC sp_helptext 'sp_MSset_oledb_prop'

You can also go and check the registry by typing “regedit” in RUN.
Go to HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> MSSQL10_50.MSSQLSERVER08R2 -> Providers
A new folder entry is made for “Microsoft.ACE.OLEDB.12.0” provider.
And Inside this folder 2 new entries are created for both the properties.
Check the image below:

Step 2:
We installed the “Access Database Engine 2010” drivers to setup a link between MS Office 2010 products and MS SQL Server.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Categories: Excel Tags: