Compare different SQL Server edition features in a smart and better way!


One thing I have noticed in forums is that people are curious about features available with different flavors of SQL Server.

Most of the times questions comes like I installed SQL Server 2008 Express and find Database Mail missing,Do I need to re-install SQL Server again?

The answer is SQL Server Express doesn’t support Database Mail.

Its not really easy to remember what feature comes in with which edition,however there is a very smart and easy comparison page provided my Microsoft.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx 

You have the option to select multiple versions and compare the features here.Hope this post will help you answer some of the feature related questions.

Thanks for reading.

Advertisements

NUMA based systems – An overview


Today more and more systems are coming with NUMA Architecture and I came across this architecture when I was dealing with wait stats.

So whats is NUMA or Non-Uniform Memory Access is all about?

At this point of time we have real fast processors and its getting faster day by day. Sometimes it has to wait for the Memory which runs at a slower speed and technically this is called as Memory latency.Most of the time the CPU has to sit idle and wait for the memory.If we can reduce memory latency,then we can definitely gain performance.

The traditional Symmetrical Memory Access or SMA was having a shared memory and CPU’s accessing that memory.

NUMA refers to a memory design option available for multiprocessor systems.In NUMA architecture each processor will have a portion of memory directly attached to it.

Lets consider this simple figure which will explain the concept well –

Under NUMA architecture for multiple processor systems there will be group of processors and memory dedicated to that group and each group is called a NUMA node.

Lets taken a example of a server with 4 processor cards and each card contains 8 processor cores and 8 memory slots.Each group is called a NUMA Node.

Each CPU can access memory associated with the other groups in a coherent way too.

Force a table to be stored in the buffer pool permanently!


There was a question from one of the users on buffer storage usage.The question was something like this

‘I want to make a table into buffer storage and keep as long as I want.Any query’s can scan this table in buffer straightly?’

This was indeed an interesting question and unfortunately the answer to the question is NO.There is no way you can achieve this in newer versions of SQL Server,ie from SQL Server 2005.

Paul S Randal whom I consider as one of the best person to talk about SQL Server had talked about a DBCC PINTABLE command which actually was used in SQL Server 2000 which will pin the pages from a heavily used.

He has confirmed that this code will does nothing in SQL 2005 or above as these newer version does buffer pool management very efficiently.

SQL Writer Error – ‘sysdatabases in SQL server instance is empty.’


You might have noticed an error in the application log for some server with in your environment which says sysdatabases in SQL server instance <Instance Name> is empty.

Cause

 

This is a SQLWriter error when we have a VSS based backup solution implemented.The backup solution is using SQL Server VSS Writer Service to connect to SQL and this account doesn’t have sysadmin rights or not part of logins.In most of the cases SQL Server VSS Writer Service will be running on Local System account.

Solution to clear the error message

 

We have to add back NT AUTHORITY\SYSTEM if it was removed and grant sysadmin rights incase Writer Service is using Local System account.

I am curious to understand if any one of you are facing this issue.

sys.messages – Datastore for all messages


This table is the datastore for all the messages in sql server system.The severity of the message is also included.I was curious to understand messages are generated in what all languages.After checking few of the system tables I was able to find that :

select * from sys.syslanguages
where lcid in(‘1033′,’1031′,’1036′,’1041′,’3082′,’1040′,’1049′,’1046′,’1028′,’1042′,’2052’)

Messages are generated in the below languages :

English
German
French
Japanese
Spanish
Italian
Russian
Brazilian
Traditional Chinese
Korean
Simplified Chinese
 

Sevierity Level 0 to 1o – Informational messages.

Sevierity Level 11 to 16 – Errors that can be corrected by users.

Sevierity Level 17 – Error because of running out of resources.

Sevierity Level 18 – Non fatal internal error,this means the task was completed,however there was an interanl error.

Sevierity Level 19 – Error and current process is terminated.

Severity Level 20 – Fatal Error in Current Process.
Severity Level 21 – Fatal Error in Database (dbid) Processes
Severity Level 22 – Fatal Error Table Integrity Suspect
Severity Level 23 – Fatal Error: Database Integrity Suspect
Severity Level 24 – Hardware Error

So next time when you get a message,you should seriously think of relating the sevierity level too which can be interesting.

Hidden gem – SP_CONFIGURE “A journey discussing each options”


I have been working on various configure options which sql will allow its users to do and they are really interesting.

Note : Changing these options should be done only after proper research and study and its not recommended to deploy this in Production environment without conduting tests in DEV/QA.

This post will explain first 18 sp_configure options :

Ad Hoc Distributed Queries:

What it means? : Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB.This method should be used for data sources which are not used frequently. Default value is 0,which means SQL server does not allow adhoc access by default.

affinity I/O mask & affinity mask:

What it means? : Both affinity I/O and affinity mask goes together. Affinity mask – Controls processors, and this should to be set after a lot of research and study. This can degrade performance. Affinity I/O will help you to nominate processors to be used for sql server I/O activity.

By default affinity mask is 0, which means that windows will decide (Algorithm) on which process needs which CPU.

Below is the table, which defines a sample affinity mask for a 8 CPU server:

By setting affinity I/O you are specifying a CPU to use only for I/O.

Affinity mask and Affinity I/O should not be set as 1.

Agent XPs :

What it means? This option is enabled to allow SQL Server Agent extended procedure and the SQL Server Agent node will be unavailable in SSMS if the value 0.

 

The default value is Zero and it Changes to 1 when SQL Server Agent is started, ie the value will be 1 if SQL Server Agent is set to automatic start during setup.)

allow updates:

What it means? By setting this option as 1,sql server will allow users will proper permission to update system tables and can create SPROC’s that update system table. It is always a best practice to keep this setting default.

awe enabled:

What it means? In SQLServer we can use the Address Windowing Extensions (AWE) API to access the physical memory in excess to the limit set on configured virtual memory.

AWE is not required and cannot be configured for 64 bit windows operating systems.

blocked process threshold :

What it means? This is a threshold in seconds, which is defined to generate reports on blocking.

 

c2 audit mode:

What it means? This feature should be turned on only if there is a specific requirement. This feature is used to track all successful and failed access to objects. This will help to track system activity and possible security violations.

The audit data is saved as a file and is saved in the default data directory and once the size reaches 200 MB a new file will be created. There is a danger of running out of space for this directory and SQL Server will shutdown on its own if this scenario occurs.

clr enabled :

What it means?  Enabling this option will ensure that user assemblies can be run in SQL Server.

CLR is a best replacement for xp_cmdshell.

 

cost threshold for parallelism:

What it means? This is a threshold at which SQL Server will create and run parallel plans for sql queries. The default value is 5.SQL Server will create and run a parallel plan only when the cost of running the single plan is more than the value mentioned for cost threshold.

Setting the value means all the queries below the value will use single plan.

This value will be considered only if you are running multiple processors.

 

cross db ownership chaining:

What it means? This option is disabled by default and should be enabled with proper knowledge on how it works and the security threat comes in with this option. If you set cross db ownership at Instance level, this means all databases will have this feature enabled and this is indeed a potential risk.

Ownership chain works like this:

User X grants permission to User Y on view Month Jan.

When User Y does a Select *, his permission is checked and as he has the permission data is retrieved.

Month Jan also require information from view Sales.SQL Server finds that User X is the owner and doesn’t check the permission of view Sales.

Sales also require information from view Invoice. SQL Server finds that User X is the owner and doesn’t check the permission of view Invoice.

When view Month Jan tries to pull information from Table Projects, the server will check if cross –database chaining is enabled, if enabled it will check the permission of the table. As User X is the owner, information is provided.

Database Mail XPs :

What it means? This will enable database email on the server.

default full-text language:

What it means? Specify a default language value for full-text indexed columns. The default setting is chosen from default language of the server.

default language:

What it means? This is the default language setting for SQL Server and default value is English.

default trace enabled:

What it means? Option to enable or disable the default trace log files.

You can read my article https://smartsqlserver.wordpress.com/2011/03/29/who-created-a-server-login-in-sql-server/ for more details on default trace.

disallow results from triggers :

What it means? This option will be removed from future version, hence its not recommended by Microsoft to use it for future deployments. This is an option to control whether triggers return result sets.

fill factor (%):

What it means? When an index is created fill factor value determines the percentage of space needs to be filled with data on the leaf node. This will reserve free space for future growth.

 

 

Shrinking file issue – Dependency of tran_log file.


Recently I observed an issue where we were trying to shrink a database file [Shrinking is not recommended in Production environment as it will cause fragmentation] and SQL Server was not shrinking the file. There was no specific error to identify the reason for this behavior.

We did some R&D and finally observed that the tran_log file was really small and almost full and this was the reason for the Shrink operation to fail.

This is one of the behavior which might happen to your environment too, incase the transaction log is too small.

I am curious to understand if anyone of you might have faced this before.