SQL Server 2005 vs SQL Server 2008

 

Overview:

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query languages are MS-SQL and T-SQL.

Below is the Release History for SQL server:

Version	Year	Release Name	Codename
1.0 (OS/2)	1989	SQL Server 1.0	-
4.21 (WinNT)	1993	SQL Server 4.21	-
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Plato
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit Edition	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai

 
 

SQL Server 2005

SQL Server 2005, released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used as a data type in database columns.

SQL Server 2008

The current version of SQL Server, SQL Server 2008, (code-named "Katmai",) was released on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects).  SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc  as well as perform search, query, analysis, sharing, and synchronization across all data types.  Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILESTREAM data type, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL;

 
 

• SQL Server 2008 delivers a rich set of integrated services that enable you to do more with your data such as query, search, synchronize, report, and analyze.
  
• SQL Server 2008 provides the highest levels of security, reliability, and scalability for your business-critical applications.
  
• SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.
  

What's new in SQL Server 2008 for SSIS

SSIS (SQL Server Integration Services) is a built in application for developing and executing ETL (extraction, transformation, and load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the necessary wizards, tools, and tasks for creating both simple import export packages, as well very complex data cleansing operations. SQL Server 2008 SSIS includes a number of improvements and enhancements such as better parallel execution. In SSIS 2005, the pipeline didn't scale past two processors. SSIS 2008 will scale past two processors on multiprocessor machines. Also, the newly redesigned pipeline improves performance on large packages that contain long sub-trees. In addition, the SSIS engine is reported to be more stable with fewer incidents of deadlocks.

 
 

Improved Scripting

SQL Server 2008 (Katmai) introduces VSTA as our new scripting engine, which replaces the old VSA engine used in SQL Server 2005 (Yukon). There are a number of advantages to the new engine, but I think the feature users are most excited about is that it allows you to use C# as a scripting language (as well as VB.NET). It also makes it easier to reference all .Net assemblies, which enables a number of common scenarios (such as easier web service, email service and custom components integration).

Merge

SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.

 

MERGE

InventoryMaster AS im

USING

(SELECT InventoryID, Descr FROM NewInventory)
AS src

ON

im. InventoryID = src. InventoryID

WHEN

MATCHED
THEN

UPDATE

SET im.Descr = src.Descr

WHEN

NOT
MATCHED
THEN

INSERT

(InventoryID, Descr)
VALUES (src. InventoryID, src.Descr);

Data Flow Task Improvements

It essentially boils down to smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase on multi-processor machines.

 
 

This graph shows much better CPU usage distribution. In this case, the package received an 80% performance increase.

Enhanced ADO.Net Support

Katmai now has an ADO.Net source (replacing the DataReader source) and destination adapters. They function a lot like the OLEDB ones, supporting a custom UI, drop down list of tables/views, and query builder. Its properties are also expression-able. This means we now support ODBC as a destination, as well.

Import/Export Wizard

The SQL Server Import/Export Wizard (which generates SSIS packages) has been improved as well.

• ADO.Net support (which gives you ODBC)
  
• Data type conversion – mappings are read from customizable (xml) files, and data convert transforms will be inserted into the data flow if required
  
• Scales better – large numbers of tables will be split across multiple data flows (up to five sources per data flow)
  

Cached Lookup

The main feature is that you can now create a lookup cache in a separate data flow using the new Lookup Cache Transform and Cache Connection Manager, and persist it to disk. This cache can be created from any data source supported by SSIS, which removes the need to stage your lookup table in a database.

This persisted cache is especially useful in scenarios where reference data is updated periodically – say, once a week. You can run a package to generate your cache when the update comes in, and re-use it in the rest of your packages until it needs to be updated again.

Another scenario is managing large lookup tables, where a small number of keys are used a large percentage of the time. For example, 5% of your customer IDs show up in 80% of your rows. Instead of caching the entire lookup table, you can use a cached lookup for the top 5%, and cascade another lookup in partial / no-cache mode for the infrequent rows.

 
 

As you can see from the screen shot, Lookup also has a new "No Match" output – rows with no matches aren't directly sent to the error output anymore. We also maintain a "Miss Cache" internally, so missing keys aren't constantly re-queried.

 
 

SQL Server 2008 Top New Features

Policy-Based Management

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

• Management is centralized, thereby reducing the need to configure each server separately
  
• Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
  
• Configuration is straightforward and can be done entirely within SQL Server Management Studio
  
• Out-of-the-box predefined policies make it easy to get started
  
• Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000
  

 
 

Performance Data Collection

The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio.

• Setup wizard makes configuration simple
  
• Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages
  

 

 

Data Compression

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

• Save disk storage
  
• Enable compression option for individual tables or indexes
  
• Configuration is easy using the Data Compression wizard
  
• Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
  
• Compression can improve disk I/O and memory utilization
  

Resource Governor

The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes

• Prioritize applications, users, and computers competing for the same resources
  
• Prevent runaway queries that hold resources for extended periods of time
  
• Limitations are not enforced when there is no competition for resources
  

Transparent Data Encryption

Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.

• Implements strong encryption keys and certificates to secure data
  
• Applications do not need to be modified to support Transparent Data Encryption
  
• Enables compliance with data privacy regulations
  
• Does not increase the size of the database
  

Data Auditing

Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.

·         Enables compliance with security regulations

·         Simple configuration using SQL Server Management Studio

·         Minimal impact on performance because audit data is stored outside of SQL Server database files

Backup Compression

Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore.

·         Save storage space

·         Compressed backups can be stored on tape or on disk

·         Simple configuration using SQL Server Management Studio

·         Default state of all backups on a server to be compressed can be configured

Table-Valued Parameters

Table-Valued Parameters (TVPs) allows stored procedures to accept and return lists of parameters. Developers can write applications that pass sets of data into stored procedures rather than just one value at a time. Table-valued parameters make the development of stored procedures that manipulate data more straightforward and can improve performance by reducing the number of times a procedure needs to call a database.

·         Eliminates the need to use less efficient methods of processing data sets, such as passing XML data into stored procedures

·         Reduces complexity and simplifies the process of working with data sets for developers

Virtual Earth Integration

Use the new spatial data types in SQL Server 2008 with Microsoft Virtual Earth to deliver rich graphical representations of the physical locations stored in a database. Use Virtual Earth support to create applications that display data about locations in desktop maps or web pages. For example, SQL Server 2008 makes it easy to show the locations of all company sites that are less than 50 kilometers from Denver.

 
 

·         Spatial data types enable geographical data to be stored natively in SQL Server2008 databases

·         Built-in library of industry-standard spatial functions streamlines development

·         Provide meaningful representations of geographical data

 
 

DATE / TIME Data Types

SQL Server 2008 introduces several new date and time based data types. DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The new DATE and TIME data types enable you to store date and time data separately. The new DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as 'plus 5 hours'.

·         Greater flexibility for storing date and time data.

·         Greater accuracy and precision for date and time data.

·         DATETIME and SMALLDATETIME data types still supported.

 
 

Partitioned Table Parallelism

Parallelism refers to using multiple processors in parallel to process a query, which improves query response time. On a multiprocessor system, SQL Server 2008 uses parallel processing when you run queries that reference partitioned tables.

When SQL Server 2008 processes such a query, rather than allocating just one processor for each partition referenced by the query, it can allocate all available processors, regardless of the number of partitions referenced.

·         Queries against partitioned tables run faster

·         More efficient use of processor resources than in previous versions of SQL Server

·         Operations involving partitioned indexes are optimized in the same way

 
 

Reporting Services

Processing and performance have been improved in SSRS (SQL Server Reporting Server). Large reports will no longer consume all available memory. In addition, there is greater consistency between layout and render. Also, the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008. Application Embedding allows URLs in reports to point to a calling application.

Microsoft Office 2007

SQL Server 2008 can tightly integrate with Microsoft Office 2007. For example, in SQL Server Reporting Server reports can now export directly to Word. In addition, both Word and Excel can be used as templates for SSRS reports by using the Report Authoring tool. Excel SSAS performance has been improved and there is a data mining add-in.

 

For anyone looking for any additional reasons to upgrade, this list may help:

Reasons To Upgrade (Worded For Management)

Upgrading to 2008 will give an additional 3 years of support. So you're looking at 7-8 years of support as opposed to 4-5 years on 2005. Source: founder of SQL Server Central. http://www.sqlservercentral.com/articles/Administration/3094/SQL 2005 SSIS – Buggy and has major shortcomings. Any enhancements for SSIS 2005 are halted.

http://ayende.com/Blog/archive/2007/07/15/SSIS-15-Faults.aspx

Cannot create a global class in a project (i.e. – you have to re-write code everywhere) CLR Framework is only a subset (Does not support External Assemblies or COM Interop)

DataTypes for transformations do not automatically map (have to manually be mapped using the mouse) It is very hard to debug a package Built-in logging displays tons of useless information and very little useful information Difficult to find out specific information on the record-level about why an error occurred Limited data types available in their flat-file connectors Data flow tasks cannot include logical conditional flows like the process flows.

Script editors use VB.Net only (…my opinion) Overall, just very sensitive and annoying. SSAS – Being a complete re-write, the 2005 Analysis services is less mature. Performance enhancements have been made in 2008. Personal experience has shown many errors when aggregating cubes and slower than expected on-the-fly aggregations than in SQL 2000 AS.

Auditing – SQL 2008 implements auditing out of the box and functions asynchronously not hindering performance. Using SQL 2005 we will have to manually write auditing functionality or go third party. Depending on future auditing equirements, performance potentially can be impacted. Resource governor – SQL 2008 provides the ability to limit the resources of queries. This often happens with reporting procedures. Limiting the resources of non application centric resources will help end-user experience.

Performance Data Collection – collect historical snapshots of system performance in a separate database Reporting Services – 2005 reporting services is very resource intensive and is not practical unless on a separate installation of SQL Server to perform report pagination/rendering. 2008 Reporting services is a rewrite of the reporting engine. http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx Full text search – This is now integrated into the SQL Server engine and performance has been enhanced. http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx Change Data Capture – For requirements to save or monitor historical information on changed data. Using SQL 2008 we can implement a non-invasive detection of changed records. CPU's can be added on the fly Ability to compress data and use less disk space

 

Reasons For Developers

 Change Data Capture (CDC) – very solid and great to use for determining changed records.

 Additional enhancements made to minimal logging for inserts

 Intellisense – Built in finally

 Resource Governor – Very cool. Throttle the resources of users based on Memory or Processor

 Declare and set a variables inline

 New Grouping Sets allows analysis-like data in OLTP (Grouping Sets)

 Table Valued Parameters – Pass a table into a stored procedure as a parameter

 Merge Statement – No more upserts. Now you can perform update and insert operations in on statement. Great for updating historical tables

 New DMV shows all object dependencies

 Data compression – Compress tables at the row level or the page level. Saves a tremendous amount of space.

 

 

Read more »

Resource Governor in SQL Server 2008

SQL Server 2008 introduces a new feature called Resource Governor which enables Database Administrators to manage SQL Server workload and critical system resource consumption. Resource Governor enables DBA to specify limits on the amount of CPU and memory which the incoming sessions to the SQL Server can use. In a production environment DBA's will come across scenarios when there could be sudden spike in CPU and memory utilization thereby resulting in slow responses to query requests. These issues happen when there is unpredicted workload execution like long running TSQL queries, database backups being performed etc. The solution to these issues is the use of Resource Governor which enables DBA's to differentiate workloads and allocate shared resources as they are requested, based on limits you specify for resources like CPU and memory. The resource governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing.

Resource Governor Concepts
The following three concepts are fundamental to the understanding and usage of Resource Governor.

Resource Pool: - A resource pool represents the physical resources of the server. There are two resource pool namely internal and default which are created when SQL Server 2008 is installed. However, SQL Server also supports the creation of user defined resource pools. In a resource pool a DBA can specify MIN or MAX value in percentages for CPU and Memory utilization. The Internal pool basically represents the resources which are consumed by SQL Server itself for its running. This pool cannot be altered by a user in any way. The default pool is a predefined user pool which contains the default group. The important thing to note is that the default pool cannot be dropped or created, however it can be altered as required.

Workload Group: - A workload group acts as a container which accepts the sessions from SQL server users, which are similar in nature based on the classification criteria that are applied to each requests. As in Resource Pool's there are two predefined workload groups namely internal and default defined by SQL Server. The users cannot alter the internal workload group but can monitor it to see how SQL Server is utilizing memory and CPU. The incoming requests to the server are classified into default workload when there is no criteria defined to classify the incoming request, or there was an attempt made to classify the requests into a nonexistent workload group or there is a failure with the classification

Classification: - Classifications are internal rules that classify the incoming requests and route then to a workload group. This classification is based on a set of user written criteria contained in a scalar function which will be created in the Master database. Once a DBA enables the Resource Governor on SQL Server then each and every single session to the server will be evaluated by the user defined classifier function.

How to Enable & Disable Resource Governor using SQL Server Management Studio
Resource Governor is by default disabled when SQL Server 2008 is installed. To enable it using SQL Server Management Studio you need to follow the below steps.

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In the Object Explorer, expand the Management Node
3. Right click Resource Governor, and then click Enable



4. Execute the following TSQL query to verify whether the Resource Governor is enabled:

SELECT * FROM sys.resource_governor_configuration

If the value is 1 for is_enabled then it means that the Resource Governors setting are set to true

5. Execute the following TSQL query to confirm that there is no pending reconfiguration of the resource governor because of changes in configuration, and the Resource Governor's configuration metadata matches it's in memory configuration. The expected value for is_reconfiguration_pending should be 0.

SELECT * FROM sys.dm_resource_governor_configuration

6. To disable the Resource Governor once enabled, you need to expand the Management Node in the object explorer. Right click Resource Governor, and then click Disable
//break
How to Enable & Disable Resource Governor using TSQL
1. Execute the below TSQL query to enable Resource Governor

Use Master
Go
ALTER RESOURCE GOVERNOR RECONFIGURE
Go

2. Execute the below TSQL query to disable Resource Governor

Use Master
Go
ALTER RESOURCE GOVERNOR DISABLE;
Go

How to create Resource Pool & Workload Group using SQL Server Management Studio
1. In the Object Explorer, expand the Management Node
2. Right click Resource Governor, and then click New Resource Pool, This will open the Resource Governor Properties page.



3. In the Resource pools grid, double click the first column and provide the values as shown in the snippet for Name, Minimum CPU %, Maximum CPU %, Minimum Memory % and Maximum Memory %

4. In the Workload groups for resource pool for PoolAdhoc, double the first column and provide the value values as shown in the snippet for Name, importance, Maximum Requests, CPU Time (sec), Memory Grant %, Grant Time-out (sec) and Degree of Parallelism.



5. A DBA also needs to alter the default resource pool with the values as shown in the snippet and then click OK to save the changes.

6. Now let's create one more Resource Pool named PoolAdmin and a Workload Group named GroupAdmin with the values as shown in the below snippet and then click OK to save the changes.



Create a Classification Function
The below TSQL query needs to be executed on the Master database to create a classification function. 

Use Master
Go
CREATE FUNCTION ClassifierResources ()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @VAL VARCHAR(32)
 SET @VAL = 'default';
 IF 'UserAdhoc' = SUSER_SNAME()
  SET @VAL = 'GroupAdhoc';
 ELSE IF 'UserAdmin' = SUSER_SNAME()
  SET @VAL = 'GroupAdmin';
 RETURN @VAL;
END
GO

The next step will be to make the function known to the Resource Governor, for that you need to execute the below TSQL statement:

Use Master
Go
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = DBO.ClassifierResources)
GO

In order to make the Resource Governor changes effective, you need to execute the TSQL statement below:

Use Master
Go
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Read more »

Shortcuts – Prakash Chheatry (December 20, 2010)

SQL Query Analyzer Hotkeys - Keyboard Shortcuts   CTRL-SHIFT-F2         Clear all bookmarks. CTRL+F2                   Insert or remove a bookmark (toggle). F2                        Move to next bookmark. SHIFT+F2                  Move to previous bookmark. ALT+BREAK                 Cancel a query. CTRL+O                    Connect. CTRL+F4                   Disconnect. CTRL+F4                   Disconnect and close child window. ALT+F1                    Database object information. CTRL+SHIFT+DEL            Clear the active Editor pane. CTRL+SHIFT+C              Comment out code. CTRL+C or Ctrl+Insert     Copy CTRL+X or Shift+Del       Cut SHIFT+TAB                 Decrease indent. CTRL+DEL                  Delete through the end of a line in the Editor pane. CTRL+F                    Find. CTRL+G                    Go to a line number. TAB                       Increase indent. CTRL+SHIFT+L              Make selection lowercase. CTRL+SHIFT+U              Make selection uppercase. CTRL+V or Shift+Insert     Paste. CTRL+SHIFT+R              Remove comments. F3                        Repeat last search or find next. CTRL+H                    Replace. CTRL+A                    Select all. CTRL+Z                    Undo. F5 or Ctrl + E            Execute a query. F1                        Help for Query Analyzer. SHIFT+F1                  Help for the selected Transact-SQL statement. F6                        Switch between query and result panes. Shift+F6                  Switch panes. CTRL+W                    Window Selector. CTRL+N                    New Query window. F8                        Object Browser (show/hide). F4                        Object Search. CTRL+F5                   Parse the query and check syntax. CTRL+P                    Print CTRL+D                    Display results in grid format. CTRL+T                    Display results in text format. CTRL+B                    Move the splitter. CTRL+SHIFT+F              Save results to file. CTRL+R                    Show Results pane (toggle). CTRL+S                    Save CTRL+SHIFT+INSERT      Insert a template. CTRL+SHIFT+M              Replace template parameters. CTRL+L                    Display estimated execution plan. CTRL+K                    Display execution plan (toggle ON/OFF). CTRL+I                    Index Tuning Wizard. CTRL+SHIFT+S              Show client statistics CTRL+SHIFT+T              Show server trace. CTRL+U                    Use database

Read more »

Overview

The release of Windows 7, the latest Microsoft operating system, is an opportunity for engineers and scientists to harness new technologies to solve technical problems. Like the predecessor, Windows Vista, users can select between a version for 32-bit processors and a version for 64-bit processors. Whether porting a system to a new version of Windows, or starting new development on the OS, it is important to understand which version best suits your needs.

Table of Contents

1. What Are the Differences between 64-Bit and 32-Bit Processors?
   
2. Evaluating an Engineering System on Windows 7
   
3. Performance and Virtualization
   
4. Potential Benefits of 64-bit
   
5. Potential Drawbacks to Windows x64 Edition
   
6. Summary
   
7. More Information on Windows 7
   

What Are the Differences between 64-Bit and 32-Bit Processors?

To understand why 64-bit operating systems are a logical step in the evolution of the PC, consider this background information. More often than not, the maximum size of the physical memory on a computer is less than the amount needed for all running programs. This is especially true when multiple processes or applications execute simultaneously. The solution for this is that programs store some of their data on the hard drive and copy it back and forth to physical memory as needed.

This solution is often referred to as "virtual memory," in which the computer simulates having large amounts of contiguous physical memory. A paging table is responsible for moving segments of virtual memory into physical memory as necessary. If the amount of memory demanded by all running processes exceeds the available physical memory (RAM), the paging table stores low-priority processes on the hard drive in the page file, which is much slower than RAM. When the user needs these processes, the page table re-maps them into physical memory, where the user can access them at high speeds (see Figure 1). The total number of addresses available in the virtual memory – the total amount of data the computer can keep in its working area for applications – is determined by the width of the registers on the computer processor.

Figure 1. The paging table moves segments of virtual memory into physical memory as needed to provide more memory to running processes.

Until recently, almost all consumer PCs used 32-bit processors. The bit size of a processor refers to the size of the address space it can reference. A 32-bit processor can reference 2^32 bytes, or 4 GB of memory. These 32-bit processors were standard at a time when 4 GB was thought to be more than enough memory space for software applications on Windows. When a process, such as running a program, is created on an x86 Windows computer with a 32-bit processor, the operating system allocates its 4 GB of virtual memory, irrespective of the actual physical memory installed on a system. Half of that allocated memory is user-accessible memory, while the other half is for kernel processes such as drivers. Modern computing systems increasingly confront the 4 GB ceiling thanks to memory-intensive applications and the need to store multiple processes in memory simultaneously.

In 2003, AMD released the first widely accepted 64-bit processor aimed at consumers, the Athlon 64, and coined AMD64 as the name for the new instruction set. Microsoft refers to the instruction set as x64, which parallels the widely accepted x86 nomenclature used for the instructions that run on most 32-bit processors. 

Processors capable of referencing larger address spaces provide the opportunity to use more physical memory than ever before, potentially reducing the overhead spent moving processes in and out of physical memory. The 64-bit processors are theoretically capable of referencing 2^64 locations in memory, or 16 exabytes, which is more than 4 billion times the number of memory locations  32-bit processors can reference. However, all 64-bit versions of Microsoft operating systems currently impose a 16 TB limit on address space and allow no more than 128 GB of physical memory due to the impracticality of having 16 TB of RAM. Processes created on 64-bit versions of Windows are allotted 8 TB in virtual memory for user processes and 8 TB for kernel processes to create a virtual memory of 16 TB.

To summarize, the ability of Windows Vista and Windows 7 x64 Editions to add address more memory space than previous versions of Windows helps minimize the time spent swapping processes in and out of memory by storing more of them in RAM.

Evaluating an Engineering System on Windows 7

Windows 7 represents the first viable upgrade from Windows XP for PC users in many ways, especially considering that Vista was largely overlooked due to various problems with compatibility and performance.  For engineering and scientific applications, the combination of Windows 7 (64-bit) and the latest version of LabVIEW and NI Device Drivers make it possible to tap into the potential of 64-bit hardware thanks to native support.

Not every application stands to benefit from the x64 architecture, and it will take time for 64-bit editions of Windows to gain widespread adoption, but the following types of applications are most likely to see performance benefits on Windows 7 x64 Edition, provided that both 64-bit application software and drivers are available:

• Applications that require mathematical precision and floating-point performance
  
• Applications that involve large, high-performance databases
  
• Vision acquisition and analysis applications with large amounts of data moving directly into memory at rapid rates
  

For more information on the availability of NI products with native support for 64-bit operating systems, please visit Announcing 64-bit Support for LabVIEW.

Performance and Virtualization

64-bit versions of operating systems such as Windows Vista and Windows 7 are not automatically faster than their 32-bit counterparts. In some cases, they may even perform slower because of the larger pointers as well unrelated OS overhead. Overall, an application's performance depends on what it is used for and how it is implemented. Emulated applications running within the Windows on Windows (WOW) 64 layer (discussed in more detail later in this article) will not be able to address any more memory than they could on a 32-bit system.

Most 32-bit software will still function because of a Microsoft emulation layer. This emulation layer, known as Windows on Windows 64 or WoW64, enables 32-bit programs to run as though on a 32-bit version of Windows by translating instructions passing in and out of 32-bit applications into 64-bit instructions. Emulated programs act as though they are running on an x86 computer and operate within the 2 GB of virtual memory that a 32-bit version of Windows allocates to every process. However, despite Wow64, 32-bit programs on 64-bit versions of Windows Vista and Windows 7 cannot take advantage of the larger 64-bit address spaces or wider 64-bit registers on 64-bit processors.

Potential Benefits of 64-bit

The transition to the 64-bit architecture is overwhelmingly driven by the limitations of the x86 architecture in terms of addressing memory. Applications running on a 64-bit edition of Windows should theoretically experience improved performance because of the larger quantity of available memory, even if the application is running within the WoW64 layer. Much like the advantage offered by increasing physical memory in a 32-bit system, the larger memory space on 64-bit versions of Windows Vista and Windows 7 allows more processes to actively reside in the system RAM simultaneously.  It allows allows users to add additional RAM beyond the previous 4 GB limit, up to the amount supported by the mainboard.

This change potentially eliminates or reduces time spent loading and switching between processes, a condition that can lead to "thrashing" when all the processor's efforts are spent merely loading and switching between threads. To reap the benefits of a 64-bit operating system such as Windows Vista x64 Edition, you should invest in a large amount of RAM (4 GB or more) and a compatible motherboard.

For 64-bit version of Windows, Microsoft also requires a digital signature on all drivers. By requiring new, signed drivers, Microsoft aims to reduce failures and improve stability by shining a spotlight on vendor responsibility for bugs. With 32-bit version of Windows, administrators can install unsigned drivers, but Microsoft continues to discourage their use. All non-legacy National Instruments drivers are digitally signed and available for both the 32-bit and 64-bit editions of Windows.

Potential Drawbacks to Windows x64 Edition

Windows XP was available only as a 32-bit operating system for 32-bit processors until 2005, when Microsoft released a 64-bit edition. This version of Windows XP did not see widespread adoption due to a lack of available software and hardware drivers. Vendors were hesitant to invest in a platform that seemed more novel than practical for consumers at the time, which led to compatibility issues with common devices such as sound and graphics cards.  Developers interested in using 64-bit operating systems found the migration path to Vista much smoother due to the WOW emulation layer, which allowed older applications to run.  However, most did not see performance increases due to a lack of applications with native support for 64-bit processors.

Given the increasing number of 64-bit processors, Windows 7 x64 Edition is unlikely to suffer the same lack of support.  A potential drawback stems from the possibility that some hardware vendors may not release Windows Vista/7 x64 Edition-compatible versions of drivers quickly.  

Summary

Windows 7 x64 Edition represents the first mainstream 64-bit operating system from Microsoft in terms of general availability and support from third-party add-on vendors. Because of this, moving from a 32-bit operating system such as Windows XP to a 64-bit operating system such as Windows 7 x64 Edition merits serious consideration in many cases.

However, the feasibility of successfully making the jump to Windows 7 x64 Edition depends largely on your application and its requirements. For some, Windows 7 x64 Edition offers much needed performance improvement, while for others, it could needlessly complicate applications designed to operate on x86 platforms or even have a negative effect on performance.

Read more »

The difference between 64 and 32 bit processors

Prakash Chheatry - Monday, December 20, 2010

In the future, the battle between 64 bit vs 32 bit processors will inevitably yield the 64 bit processor as the victor, but this transition is going to take some time.

First, I'll talk about the pure mathematics and structure of the processors that are involved here. I'll keep this part short and sweet.  

A bit is short for "binary digit." It is basically how a computer stores and makes references to data, memory, etc. A bit can have a value of 1 or 0, that's it. So binary code is streams of 1's and 0's, such as this random sequence 100100100111. These bits are also how your processor does calculations. By using 32 bits your processor can represent numbers from 0 to 4,294,967,295 while a 64-bit machine can represent numbers from 0 to 18,446,744,073,709,551,615. Obviously this means your computer can do math with larger numbers, and be more efficient with smaller numbers.

Now see, that description wasn't too bad, but the question is how does this affect you, the average PC owner? The largest benefit will go to academic institutions and private companies, where large calculations are being performed, huge databases are being accessed, and complex problems are being solved.

Everyone that doesn't fall into that category will see some benefit of using 64 bit processors over 32 bit processors, but not much in today's marketplace. The AMD Athlon 64-bit processor is completely backward compatible, meaning you can currently use it with 32-bit operating systems and software programs. You will see some benefits by using this setup, but because the programs weren't written to take advantage of the extra power, they won't use much of it.

The true benefits of this set up don't come from the amount of bits, but by the improved structure of the 64 bit vs 32 bit processor's older structure. A 64-bit processor is made with more advanced silicon processes, have more transistors, and faster speeds. This is currently where the true benefit of switching to a 64-bit processor lays.

As for 64-bit operating systems and software, many are in the works, but nothing is in final version. Microsoft has released a beta version of Windows XP that takes advantage of the 64 bit technology, but there are still issues. The problem is when you run 32-bit software programs in the environment of a 64-bit operating system. Many programs won't work properly, such as Adobe Acrobat and Windows Media Player, for example. Another issue is RAM. You really need about 4 GB of RAM to take full advantage of the capabilities offered by a 64-bit processor, while most PC owners have less than 1 GB under their computer's hood.

So, the question now is should you buy a 64 bit processor now, or wait?

Disadvantages:

You're currently not able to take full advantage of the technology because the software vendors haven't made the switch from 32-bit to 64-bit processors.

Most AMD Athlon 64 bit processors are expensive, with prices sure to go down in the future.

Advantages:

Better performance out of a 32-bit operating system.

Probably the last processor you'll have to buy for many years to come.

Read more »

Change Data Capture

As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Change Data Capture.  Can you give us a detailed explanation of how we go about using this one?

Solution
Change Data Capture is a new feature in SQL Server 2008 that records insert, update and delete activity in SQL Server tables.  A good example of how this feature can be used is in performing periodic updates to a data warehouse.  The requirement for the extract, transform, and load (ETL) process is to update the data warehouse with any data that has changed in the source systems since the last time the ETL process was run.  Before CDC we might simply query a last updated DATETIME column in our source system tables to determine what rows have changed.  While this is simple and pretty effective, it is of no use in determining any rows that were physically deleted.  In addition we can't determine what was changed when; we can only access the current state of a row that has changed.  CDC provides a configurable solution that addresses these requirements and more.

In this tip we are going to gain an understanding of CDC by walking through a simple code sample to demonstrate how to:

• Setup and configure CDC
  
• Use CDC to extract rows that have been inserted, updated, or deleted via T-SQL queries
  

Before we start reviewing the sample T-SQL code, let's discuss how CDC works at a high level.  After performing some setup and configuration steps (which we will cover below), CDC will begin scanning the database transaction log for changes to certain tables that you specify, and will insert these changes into change tables.  These change tables are created during the setup and configuration process.  The setup and configuration process will also create table-valued functions which can be used to query for the changes.  You use the table-valued functions in lieu of querying the underlying change tables directly.  Based on this high level description, let's proceed to the demo.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008.  Some of the function names and stored procedure names have changed from the earlier CTPs.

Setup and Configuration

CDC is a feature that must be enabled at the database level; it is disabled by default.  To enable CDC you must be a member of the sysadmin fixed server role.  You can enable CDC on any user database; you cannot enable it on system databases.  Execute the following T-SQL script in the database of your choice to enable CDC:

declare @rc int exec @rc = sys.sp_cdc_enable_db select @rc -- new column added to sys.databases: is_cdc_enabled select name, is_cdc_enabled from sys.databases

The sys.sp_cdc_enable_db stored procedure will return 0 if successful and 1 if it fails.  You can query whether CDC is enabled for any database by checking the new column is_cdc_enabled in the sys.databases table.  You will see a value of 1 if CDC is enabled, a 0 otherwise.

The next step is to specify a table that you want to enable for CDC.  Let's create a simple table called customer:

create table dbo.customer ( id int identity not null , name varchar(50) not null , state varchar(2) not null , constraint pk_customer primary key clustered (id) )

Execute the following system stored procedure to enable CDC for the customer table:

exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'customer' , @role_name = 'CDCRole', @supports_net_changes = 1 select name, type, type_desc, is_tracked_by_cdc from sys.tables

You must be a member of the db_owner fixed database role in order to execute the above system stored procedure and SQL Agent must be running.  The sys.sp_cdc_enable_table system stored procedure has quite a few parameters; let's describe each one (only the first three parameters are required; the rest are optional and only the ones used are shown above):

• @source_schema is the schema name of the table that you want to enable for CDC
  
• @source_name is the table name that you want to enable for CDC
  
• @role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist.  You can add users to this role as required; you only need to add users that aren't already members of the db_owner fixed database role.
  
• @supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
  
• @capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
  
• @index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
  
• @captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
  
• @filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
  
• @partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed; i.e. allowing you to enable partitioning (TRUE or FALSE).
  

There is a new column named is_tracked_by_cdc in sys.tables; you can query it to determine whether CDC is enabled for a table.

Enabling CDC at the database and table levels will create certain tables, jobs, stored procedures and functions in the CDC-enabled database.  These objects will be created in a schema named cdc and a cdc user is also created.  You will see a message that two SQL Agent jobs were created; e.g. cdc.sql2008demo_capture which scans the database transaction log to pickup changes to the tables that have CDC enabled, and cdc.sql2008demo_cleanup which purges the change tables periodically.  The naming convention is cdc.[databasename]_task.  We will discuss some of the schema objects created as we use them in the demo that follows.  You can examine the schema objects created by running the following T-SQL script:

select o.name, o.type, o.type_desc from sys.objects o join sys.schemas s on s.schema_id = o.schema_id where s.name = 'cdc'

You can disable CDC on a particular table by executing the following T-SQL script:

exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'customer', @capture_instance = 'dbo_customer' -- or 'all'

You can disable CDC at the database level by executing the following T-SQL script:

declare @rc int exec @rc = sys.sp_cdc_disable_db select @rc -- show databases and their CDC setting select name, is_cdc_enabled from sys.databases

Disabling CDC at the table and/or database level will drop the respective tables, jobs, stored procedures and functions that were created in the database when CDC was enabled.

Now that we have enabled CDC for a database and a table, let's proceed to a demo.

CDC Demo

We will perform the following steps to demonstrate the CDC functionality:

• Perform a couple of inserts, update, and deletes to the customer table
  
• Show T-SQL code samples to query the changes
  

Execute the following T-SQL script to perform some inserts, an update, and a delete on the customer table:

insert customer values ('abc company', 'md') insert customer values ('xyz company', 'de') insert customer values ('xox company', 'va') update customer set state = 'pa' where id = 1 delete from customer where id = 3

Now let's take a look at a query that will show us a record of the above changes:

declare @begin_lsn binary(10), @end_lsn binary(10) -- get the first LSN for customer changes select @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer') -- get the last LSN for customer changes select @end_lsn = sys.fn_cdc_get_max_lsn() -- get net changes; group changes in the range by the pk select * from cdc.fn_cdc_get_net_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); -- get individual changes in the range select * from cdc.fn_cdc_get_all_changes_dbo_customer( @begin_lsn, @end_lsn, 'all');

To extract the changes for a table that has CDC enabled, you have to supply the relevant LSNs.  An LSN is a log sequence number that uniquely identifies entries in the database transaction log.  If this is the first time you are querying to extract changes, you can get the minimum LSN and the maximum LSN using the functions sys.fn_cdc_get_min_lsn() and sys.fn_cdc_get_max_lsn().  If you set @supports_net_changes = 1 when enabling CDC on the table, you can query for the net changes using cdc.fn_cdc_get_net_changes_dbo_customer().  This will group multiple changes to a row based on the primary key or unique index you specified when enabling CDC.  You can always invoke cdc.fn_cdc_get_all_changes_dbo_customer() to retrieve every change to the table within the LSN range.  The dbo_customer portion of the function name is the capture instance; this is the default - schema_tablename.  Here's the output from the above query:

The first result set shows the net changes; the second result set shows the individual changes.  Since there was an insert and a delete, the first result set doesn't show that row since it was added and deleted in the LSN range; i.e. it no longer exists when you group the changes.  The __$operation column values are: 1 = delete, 2 = insert,  3 = update (values before update), 4 = update (values after update).  To see the values before update you must pass 'all update old' to the cdc.fn_cdc_get_all_changes_dbo_customer() function.  The __$update_mask column is a bit mask column that identifies the columns that changed.  For __$operation = 1 or 2, all columns are indicated as changed.  For __$operation = 3 or 4, the actual columns that changed are indicated.  The columns are mapped to bits based on the column_ordinal; execute the stored procedure sys.sp_cdc_get_captured_columns passing the capture instance as a parameter to see the column_ordinal values; for example:

Let's extend this example to handle periodically extracting changed rows.  We will add a new table to log the ending LSN and a new function to retrieve the ending LSN from the table.  This will allow us to pick up just what changed since the last time we ran our ETL process.

create table dbo.customer_lsn ( last_lsn binary(10) ) create function dbo.get_last_customer_lsn() returns binary(10) as begin declare @last_lsn binary(10) select @last_lsn = last_lsn from dbo.customer_lsn select @last_lsn = isnull(@last_lsn, sys.fn_cdc_get_min_lsn('dbo_customer')) return @last_lsn end

We'll modify the code above adding a call to the get_last_customer_lsn() function and an insert or update to save the ending LSN:

declare @begin_lsn binary(10), @end_lsn binary(10) -- get the next LSN for customer changes select @begin_lsn = dbo.get_last_customer_lsn() -- get the last LSN for customer changes select @end_lsn = sys.fn_cdc_get_max_lsn() -- get the net changes; group all changes in the range by the pk select * from cdc.fn_cdc_get_net_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); -- get all individual changes in the range select * from cdc.fn_cdc_get_all_changes_dbo_customer( @begin_lsn, @end_lsn, 'all'); -- save the end_lsn in the customer_lsn table update dbo.customer_lsn set last_lsn = @end_lsn if @@ROWCOUNT = 0 insert into dbo.customer_lsn values(@end_lsn)

After running the above query once, insert a row, then run the query again.  Your output will look like this, showing only a single row that has changed:

Read more »