SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for different purposes.

Temporary tables were designed for storage and  manipulation of temporal data.

Table variables were designed to return User Defined Function (UDF) datasets.

 Which type should be used ?

If I estimate the data sets are smaller than 75,000 and no SQL Server indexes  are required Table variables are worth investigating.  Always test both  scenarios as part of design and acceptance testing.

Consider SQL Server test data generation testing tools   and then run some artifical queries for performance testing.

Feature comparison of Table variable and Temporary Table

Feature	Table variable	Temporary table
Constraint type	Primary Key, Unique Key , NULL	All except Foreign Key
Parallel execution plans while modifying table	No	Yes
Parallel execution plans while read-only	Yes	Yes
SELECT,INSERT,UPDATE ,DELETE	Yes	Yes
Indexes	No (except Clutsered Index)	Yes
Statistics	No	Yes
Assignment operation b/w variable	No	Yes
Affected by transaction rollbacks	No
Partitioning	No	No
Stored in sysobjects	No	Yes
Instantiated in TempDB	Yes	Yes
Altered by DDL	No	Yes
DML statement scope	1	multiple

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk.  The purpose of  asynchronous IO is to release resources and  for other tasks to progress.

i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur.

 When this occurs , you’ll see an error message IO Requests taking longer than 15 seconds to complete

IO  writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server memory region

When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.

Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging

The write-ahead logging method ensure the transaction log is written to disk  and is fundamental to the Recovery Manager.

How is the dirty page written to disk?

Eager writing   - Nonlogged bcp,  SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.

Checkpoint  writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.

Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

a)       net stop mssqlserver

b)       SHUTDOWN

c)       ALTER DATABASE adding a file

 

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Migrating to SQL Server from another Database platform

Migrating to SQL Server from another Database platform has a number of considerations

1)   Create an inventory of existing applications. Separate into off-the-shelf and in-house

2)   Ensure sufficient licenses on existing database platform can be retired to enable maintenance costs to decrease.

3)   For off-the-shelf .

      Does the vendor support  SQL Server?

      If they don’t is there a timeline to introduce SQL Server support?

Usually vendors won’t support an application if the database platform is not certified.    Application functionality may rely of database specific functions. Migrating would require full-compatibility analysis.

 Applications relying on built-in database administration can be more complicated to  migrate

 

4)     For in-house:

   Which existing features aren’t supported by SQL Server?

Discuss with SQL Server Consultant , the level of risk associated with migrating onto   SQL Server from another platform.

 Design a Proof of Concept plan . The key question should be “which features are not compatible?” with a follow-up question “how much work/cost is required to  redesign/program?”

 5)     Two-tiered/three-tiered. As a “rule of thumb” I’ve found three-tiered applications easier to migrate onto SQL Server.  Two-tiered applications often have all the logic in the sql language layer , resulting in complex objects , difficult to untangle.

6)    Business Case.    Migration is an expensive task. Creating a case to justify switching to another database platform is important. Complete a full cost-analysis and present to management.

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Database Cost Savings

I watched a video recently by Forester Research  , discussing Database Cost Savings. The video was intended for IT decision makers, Senior DBAs and systems managers. There were some interesting points  but all require careful analysis before implementation

The main message was : the current economic environment requires rethinking of database costs. The traditional methods of consolidations, migration and upgrades are not keeping costs down .

The main trends in database management are  :

1)  Data Volumes are growing . In some environments data is doubling every few months.

2)   Large databases – analyse  the impact on : performance , backups, availability.  Multi-terabyte databases are normal.

3)  New business requirements – compliance, real time

4)  IT budgets are under pressure to decrease

5)  Improved SLAs – user are increasing on a 24 x 7 basis

 New Approaches

1)  Virtualized infrastructure – Within the virtualized infrastructure there are two approaches:

a)  multiple VMs on a host server

b)  Clustering multiple servers for virtualized environments

Virtualizing database servers has many benefits – but extracting the performance from IO and CPU intensive servers is a challenge.

Read Database Virtual Server Candidacy Criteria

Read Virtualization – and database servers

2)  Database compatibility layers (DCL)

“database access layer that supports another database management system's (DBMS's) proprietary extensions natively, allowing existing applications to access the new database transparently”  This is a new feature of DB2 9.7

Ability to run an applications against another DBMS natively

The main benefit is it requires minimal changes

3) Database as a service

Delivers a database readily on demand to support any application. The licensing models are based on use.  It allows capacity on demand.

4)  Database compression

It’s not new , but now based on row\page . Hard disk space is saved and query performance can increase.  Weigh up against increased CPU.

 Read Data compression in SQL Server 2008

 5)  Database sub setting

Database sub setting is a method of creating referentially accurate, stripped down versions of a Production database.  This version is used in Development or Test environments

6) Automation administration

Tuning , backups , security

Read SQL Server DBA Top 10 automation tasks

Other traditional options are:

1)  Database and server consolidation

2)  Open source databases

3)  Data archiving

4)   Managed services and outsourcing

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Datawarehouse Trends – In-memory

What does the emergence and growth of In-memory databases mean for  Datawarehouse? 

 1)  In-Memory database server systems are  fast.  Consideration should be given to hardware purchases, configuration and consolidation. I’ve been working with solidDB over the last few months and the speed differences are obvious. The Query response and commit times are fast.

 2)   Traditionally analytics\DSS databases are maintained separately from transactional systems. Characterised by different memory, storage requirements and IO patterns. DBAs maintain Datawarehouses based on established principles.

In-Memory databases encourage analytics and transactional to use the same     database , on the same IO subsystem.  DBAs will need to consider this approach and  analyse the implications. For example, can Resource Governor manage cap resource usage per different type of activity? How  can this be managed?

3)  Databases will tend towards  the middle tier. A mixture of DSS models, master data framework and database as a service may become a favoured architecture.  Consolidation models may change.

4)   Row based versions of RDBMS (OLAP\DSS) may migrate to a column based approach and in-memory architecture. Initially I can see benefits for read-only data sets such as OLAP. Mainly because OLAP is characterised by complicated queries over large data sets.

 One benefit of column based approach is data compression. If the column data is of the same data type , are the compression ratios more effective?

 5)    BI solutions will exploit IMDB database systems. The increased performance will make the BI solutions more attractive. Investment may increase, leading to more development , mainstream products.

I’d like to hear your thoughts .

Author: Jack Vamvas (http://www.sqlserver-dba.com)