SQL Server 2008 licensing

When choosing a licensing structure for SQL Server 2008 consideration should be given to the different licensing models:

 a)Server plus device client Access license (CAL)

b)Server plus user client access license (CAL)

c)Processor  license

 In calculating efficient pricing ,consider such factors as:

 a)Can you define the upper limit of users? This could  have an impact on , either deciding the Processor license  or Server plus CAL

b)Are you using multiple instances on one operating system?

c)Are you using active\passive scenarios? Such as in failover clustering,mirroring and log shipping?

 SQL Server 2008 Licensing

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

SQL server RESTORE detected an error on page (0:0) in database as read from the backup set.

There is a nightly backup | copy | & restore job as part of  an ETL. Last night the job failed – with the error :

 RESTORE detected an error on page (0:0) in database MYDB as read from the backup set.

 The correlating message in the Event Viewer is :

The backup data at the end of "J:\mydrive\mydb.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

 Speaking to the SAN administrator , there were some IO issues which coorelated at the same time . I reran the job approximately 1 hr later and the job worked OK.

 I’ve added a RESTORE VERIFY ONLY

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

SQL Server Collations and code page

A brief note on collations. In this case:

SQL_Latin1_General_CP1_CI_AS

 V

 SQL_Latin1_General_CP850_CI_AS

 What is the difference?

 Firstly , some terminology :

Code Page – 1-4 digit number specifying code page . This code page define the bit patterns for the characters

Case Sensitivity  - CS – case sensitive , CI - insensitive

Accent Sensitivity -  AI – accent insensitive , AS accent sensitive

 In the collations given above ,

CP1 = code page 1252

CP850 = code page 850

 Code page 1252 is Latin(ANSI)

Code Page 850 is Multilingual(MS-DOS Latin1)

 Looking at the 2 collations above – they are :

1)Both SQL collations

2)They are both Case Insensitive and Accent sensitive

3)The use different code pages.

 It is important to remember that whatever code page is chosen , it must be supported by the underlying operating system. If the users are using the same language that is straightforward, if different users are using different languages in the same instance, than collation choices need to be adjusted.

 Further , in the Multilanguage environment – consideration should be given to using Unicode based columns – (nchar,nvarchar,ntext) . This allows more effective code page conversion

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

SQL Server – Disk Queue length

 The Disk Queue length , in itself , cannot full explain the underlying I/O subsystem delays , but is a good indicator that disk bottleneck is occurring.

 PhysicalDisk: Current Disk Queue Length  measures  how many requests are waiting for disk access. In conjunction with PhysicalDisk: % Disk Time , which checks the percentage of time the disk spends in readwrite. Sustained high values are a strong indicator that I/O problems exist.

 From the SQL server perspective , it can be involved to gauge in detail which file(s) could be experiencing the issue. The sys.dm_io_virtual_file_stats view returns I/O Statistics for data/log files per file  , and is useful in breaking down transaction i/o rates per file.

Read more on : SQL Server measure transaction rates and sys.dm_io_virtual_file_stats

Related :IO Requests taking longer than 15 seconds to complete

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

SQL Server Materialized Views

In a conversation with a colleague  we started discussing materialized views. Firstly, he wanted to know do materialized views exist in SQL server ? and if so, how are the implemented and maintained for decent performance.

 Materialized do exist in SQL server – commonly refered to as Indexed Views.

 It is worth creating outlining some definitions.

 

Materialized we mean that the data in the view is actually computed and stored.

View  means a virtual table where the contents are defined by a query. Apart from indexed views ,the “standard” view is not a stored dataset – but is a set of columns and rows where the data set is derived from the underlying query.

 As well as the storing of underlying data, the other main difference between a normal view and an Indexed view – is that the Indexed view has a unique clustered index.

As for performance – an indexed view can perform better given the circumstances. For example , if the dataset is not updated frequently and the query deals with large aggregations.

Or if there is large aggregation datasets with many joins and various queries are used to access different aspects of the data.

 If regular updates are required on the Indexed View – the maintenance level overheads may outweigh the benefits derived.

 In situations where there are OLTP type queries and also frequent updates – indexed views can cause performance to suffer.

 Ensure performance testing takes into account  this type of analysis

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