Hardware RAID Levels

RAID	Minimum	Description	Strengths	Weaknesses
Level	Number
	of Drives
RAID 0	2	Data striping without redundancy	Highest performance	No data protection; One drive fails, all data is lost
RAID 1	2	Disk mirroring	Very high performance; Very high data protection; Very minimal penalty on write performance	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required
RAID 2	Not used in LAN	No practical use	Previously used for RAM error environments correction (known as Hamming Code ) and in disk drives before the use of embedded error correction	No practical use; Same performance can be achieved by RAID 3 at lower cost
RAID 3	3	Byte-level data striping with dedicated parity drive	Excellent performance for large, sequential data requests	Not well-suited for transaction-oriented network applications; Single parity drive does not support multiple, simultaneous read and write requests
RAID 4	3 (Not widely used)	Block-level data striping with dedicated parity drive	Data striping supports multiple simultaneous read requests	Write requests suffer from same single parity-drive bottleneck as RAID 3; RAID 5 offers equal data protection and better performance at same cost
RAID 5	3	Block-level data striping with distributed parity	Best cost/performance for transaction-oriented networks; Very high performance, very high data protection; Supports multiple simultaneous reads and writes; Can also be optimized for large, sequential requests	Write performance is slower than RAID 0 or RAID 1
RAID 0/1	4	Combination of RAID 0 (data striping) and RAID 1 (mirroring)	Highest performance, highest data protection (can tolerate multiple drive failures)	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required; Requires minimum of four drives
RAID 1/0	4	Combination of RAID 1 (mirroring) and RAID 0 (data striping)	Shares the same fault tolerance as RAID 1 (the basic mirror), but compliments said fault tolerance with a striping mechanism that can yield very high read rates	High redundancy cost overhead; Because all data is duplicated, twice the storage capacity is required; Requires minimum of four drives

 

SQL Server Partial Backups

OverviewA new option is "Partial" backups which was introduced with SQL Server 2005. This allows you to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option if you have Read-Only filegroups in the database and do not want to backup the entire database all of the time.
ExplanationA Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. If a filegroup is changed from Read-Only to Read-Write it will be included in the next Partial backup, but if you change a filegroup from Read-Write to Read-Only you should create a filegroup backup, since this filegroup will not be included in the next Partial backup.
A partial backup can be completed only by using T-SQL. The following examples show you how to create a partial backup.

---

Create a partial backup of the TestBackup database
For this example I created a new database called TestBackup that has three data files and one log file. Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup. The code below shows how to do a partial backup.

T-SQL
Create a Differential Partial Backup

BACKUP DATABASE Test READ_WRITE_FILEGROUPS
TO DISK = 'C:\Test_Partial.BAK'
GO
Create a Differential Partial Backup

BACKUP DATABASE Test READ_WRITE_FILEGROUPS
TO DISK = 'C:\Test_Partial.DIF'
WITH DIFFERENTIAL
GO

QUERY TO FIND ALL THE PROCESSES RUNNING ON PARTICULAR DATABASE

SELECT [Database]=DB_NAME(dbid), spid, last_batch,status, hostname, loginame

FROM sys.sysprocesses

WHERE dbid=DB_ID('SYSTEMTEST'); ----change the databse name here

How many SQL Server Instances are installed on a server?

-- Create Temporary table to store the data

Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

-- Read Data from Registery

Insert into #SQLInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select InstanceName from #SQLInstances

-- Clear the temp table
drop table #SQLInstances

Script to Find SQL Server Cluster Shared Drives

-- SQL Script to Find SQL Server Cluster Shared Drives, Using Function

SELECT * FROM fn_servershareddrives()

-- SQL Script to Find SQL Server Cluster Shared Drives, Using DMV

SELECT * FROM sys.dm_io_cluster_shared_drives

Script to Find SQL Server Error Log location

 

SELECT SERVERPROPERTY('ErrorLogFileName')

SQL Script to check blocking and blocked processes

SELECT  x.session_id,
        x.host_name,
        x.login_name,
        x.start_time,
        x.totalReads,
        x.totalWrites,
        x.totalCPU,
        x.writes_in_tempdb,
    (
            -- Query gets XML text for the sql query for the session_id
            SELECT      text AS [text()]
            FROM  sys.dm_exec_sql_text(x.sql_handle)
            FOR XML PATH(''), TYPE
     
    )AS sql_text,
     COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
    (
        SELECT p.text
        FROM
        (
            -- Query gets the corresponding sql_handle info to find the XML text in the next query
            SELECT MIN(sql_handle) AS sql_handle
            FROM sys.dm_exec_requests r2
            WHERE r2.session_id = x.blocking_session_id
        ) AS r_blocking
        CROSS APPLY
        (
            -- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
            SELECT text AS [text()]
            FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
            FOR XML PATH(''), TYPE
        ) p (text)
    ) AS blocking_text
FROM
(
-- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle
    SELECT  r.session_id,
            s.host_name,
            s.login_name,
            r.start_time,
            r.sql_handle,
            r.blocking_session_id,
            SUM(r.reads) AS totalReads,
            SUM(r.writes) AS totalWrites,
            SUM(r.cpu_time) AS totalCPU,
            SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
    FROM    sys.dm_exec_requests r
    JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
    JOIN    sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
    WHERE   r.status IN ('running', 'runnable', 'suspended')
      and r.blocking_session_id <> 0
    GROUP BY    r.session_id,
                s.host_name,
                s.login_name,
                r.start_time,
                r.sql_handle,
                r.blocking_session_id
) x

Internals of TempDB

Tempdb is a critical resource in SQL Server.

It is used internally by the database engine for many operations, and it might consume a lot of disk space. In the past two weeks I encountered 3 different scenarios in which tempdb has grown very large, so I decided to write about troubleshooting such scenarios.
Before I describe the methods for troubleshooting tempdb space usage, let’s begin with an overview of the types of objects that consume space in tempdb. There are 3 types of objects stored in tempdb:

• User Objects
• Internal Objects
• Version Stores

A user object can be a temporary table, a table variable or a table returned by a table-valued function. It can also be a regular table created in the tempdb database. A common misconception is that table variables (@) do not consume space in tempdb, as opposed to temporary tables (#), because they are only stored in memory. This is not true. But there are two important differences between temporary tables and table variables, when it comes to space usage:

1. Indexes and statistics on temporary tables also consume space in tempdb, while indexes and statistics on table variables don’t. This is simply because you cannot create indexes or statistics on table variables.
2. The scope of a temporary table is the session in which it has been created, while the scope of a table variable is the batch in which it has been created. This means that a temporary table consumes space in tempdb as long as the session is still open (or until the table is explicitly dropped), while a table variable’s space in tempdb is deallocated as soon as the batch is ended.

Internal objects are created and managed by SQL Server internally. Their data or metadata cannot be accessed. Here are some examples of internal objects in tempdb:

• Query Intermediate Results for Hash Operations
• Sort Intermediate Results
• Contents of LOB Data Types
• Query Result of a Static Cursor

Unlike user objects, operations on internal objects in tempdb are not logged, since they do not need to be rolled back. But internal objects do consume space in tempdb. Each internal object occupies at least 9
pages (one IAM page and 8 data pages). tempdb can grow substantially due to internal objects when queries that process large amounts of data are executed on the instance, depending on the nature of the queries.

Version stores are used for storing row versions generated by transactions in any database on the instance. The row versions are required by features such as snapshot isolation, after triggers and online index build. Only when row versioning is required, the row versions will be stored in tempdb.
As long as there are row versions to be stored, a new version store is created in tempdb approximately every minute. These version stores are similar to internal objects in many ways. Their data and metadata cannot be accessed, and operations on them are not logged. The difference is, of-course, the data that is stored in them.

When a transaction that needs to store row versions begins, it stores its row versions in the current version store (the one that has been created in the last minute). This transaction will continue to store row versions in the same version store as long as it’s running, even if it will run for 10 minutes. So the size of each version store is determined by the number and duration of transactions that began in the relevant minute, and also by the amount of data modified by those transactions.

Version stores that are not needed anymore are deallocated periodically by a background process. This process deallocates complete version stores, not individual row versions. So, in some cases, it might take a while till some version store is deallocated.
There are two types of version stores. One type is used to store row versions for tables that undergo online index build operations. The second type is used for all other scenarios.

Since the release of SQL Server 2005 there are 3 dynamic management views, which make the task of troubleshooting tempdb space usage quite easy. The views are:

• sys.dm_db_file_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_task_space_usage

All 3 views return a column named “database_id”, so you might think that they return information for all the databases in the instance, right? Wrong! These views return information for the tempdb database only, so the value returned in this column is always “2” (the database ID of tempdb).

The first view (sys.dm_db_file_space_usage) returns space usage information for each data file in tempdb. It gives a high level distribution of the space occupied by tempdb.

Identifying long running tran as part of row-versioning, applicable on in 2005 & 2008

SELECT ses.original_login_name,trn.session_id,transaction_id, rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours
'+rtrim(ltrim(str((elapsed_time_seconds/60)%60)))+' Minutes'
as 'row versioning since', ses.status,
[host_name]--,last_request_start_time,last_request_end_time
FROM sys.dm_tran_active_snapshot_database_transactions trn
inner join sys.dm_exec_sessions ses on ses.session_id=trn.session_id
ORDER BY elapsed_time_seconds DESC;

Identify queries that are generating the most IOs

SELECT TOP 10

(total_logical_reads/execution_count) AS

avg_logical_reads,

(total_logical_writes/execution_count) AS

avg_logical_writes,

(total_physical_reads/execution_count)

AS avg_phys_reads,

execution_count,

statement_start_offset as stmt_start_offset,

(SELECT SUBSTRING(text, statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

ELSE statement_end_offset

END - statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

plan_handle

FROM sys.dm_exec_query_stats

ORDER BY

(total_logical_reads + total_logical_writes) DESC

Find the oldest transactions that are active and using row versioning

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds, b.program_name, b.open_tran, b.status FROM sys.dm_tran_active_snapshot_database_transactions a join sys.sysprocesses b on a.session_id = b.spid ORDER BY elapsed_time_seconds DESC

Strategy to deign index for new database/table

It is recommended to take a strategic approach while you start designing index for a new database or new table/s for optimal performance and use of resources. Please consider using below strategy to design indexes for a newly created database/table/s:

PHASE 1: INITIAL DESIGN

This phase deals with designing index for a newly created database/table/s before moving it to production. Please follow below steps to design index for the database/table/s:

1. Start with each table and go one by one. Identify all queries which will run on the table and identify a best clustered index candidate for the table.

2. Select Primary Key and unique key. Do not blindly make your primary key as a clustered index. It is fine if your clustered index is not primary key.

3. Have foreign key are in place where ever needed. Foreign keys are not that bad for performance as long as you have index on this. So make sure you create non clustered index on the columns which are part of foreign key.

4. Create non clustered index for highly selective queries. Highly selective queries are the queries that will be frequently used and their performances are critical to business. Prefer to use composite (wide) index instead of having multiple smaller index but do not go for covering index.

5. STOP INDEX DESIGN AND IMPLEMENTATION AT THIS STAGE AND MOVE AHEAD WITH YOUR FINAL LOAD TEST AND/OR USER ACCEPTANCE TEST WHERE YOU CAN COLLECT TRUE WORKLOAD FROM PRODUCTION LOAD SCENERIO.

6. Gather true work load from load test and/or user acceptance test and analyze it and then design other non-clustered indexes. Follow below rules while you create such indexes:

a. Be sure that the workload you are working with truly represents the production scenario. A workload which captures partial scenario can lead us take decision which may degrade application’s performance.

b. Use available tools to analyze the workload and situation. Most common tools will be DTA (Database Tuning Advisor) and Missing Index Hint from Query Plan. However both tool has it’s limitation so do not blindly follow their recommendation.

7. MOVE THE DATABASE/TABLES TO PRODUCTION AT THIS STAGE. HOWEVER PLEASE REFER TO PHASE 2 FOR MAINTEAINCE OF INDEX FROM DESIGN PRESPECTIVE WHILE DATABASE/TABLE/S ARE IN PRODUCTION.

PHASE 2: DYNAMIC MAINTENANCE

Applications changes with time. This means database objects and server configuration changes which can make some index irrelevant or harmful for some queries or ma require new index. So once the database/tables are in production you need to keep evaluating the index design. You can execute below strategies to accomplish this task:

Proactive: Ensure that you capture good workload and execute Step 6 of PHASE 1 of this post at a realistic time interval. This time interval should be based on the database/application’s performance history and other relevant parameters. However you need to be very careful to ensure that you do not adversely affect existing system/s while working on this proactive maintenance.

Reactive: When you move a new code (change) to production, make sure that indexing is designed properly.

* Note: In this post I outlined a general strategy to design and implement index for new application/database/table/s which should work for almost any situation. However good index design comes from extensive experience and deep knowledge. Also consider using other options (like indexed view, code review etc) instead of completely relying on index for achieving desired performance.

Wide tables in SQL Server 2008

SQL Server table supports up to 1024 columns per table which is typically good enough for most of the cases. However if you need to expand a table beyond 1024 columns then you need to use a special table types named as Wide Tables. Wide tables are tables which can have more than 1024 columns and can actually have up to 30,000 columns. For in depth details on wide table, you may want refer http://msdn.microsoft.com/en-us/library/ms186986.aspx

 

Wide table uses sparse columns to increase the total of columns. This technology creates issues like performance problems, complications with index management, limits execution of DML statements, reduce performance for switch partition operations, prevents using compression etc. Hence unless absolutely necessary, it is not advisable to use wide tables. You may recommend using other technologies like joins or xml instead of using wide tables.

For more details on possible performance consideration for wide tables please refer to http://msdn.microsoft.com/en-us/library/cc645884.aspx

How to create a wide table? (How to create a table which will have more than 1024 columns?)

Step 1:
Create a column set on the table using below code: [Note: This must be first step before adding any sparse column to the table. If the table already has any sparse column then you cannot add column set. Also if you cannot add a column set then your table can not have more than 1024 columns even if you attempt to add new columns as sparse column]

ALTER TABLE [table name]
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO
Example:
ALTER TABLE dbo.table1
ADD Widecolumn1 varchar(10) SPARSE NULL ;
GO
Step 2:

Now you can add columns and widen the table as you need.

[Note: New columns must be sparse columns assuming you already have 1024 traditional (non-sparse) columns. You cannot add more than 1024 non-sparse columns even if the table has column set.]

ALTER TABLE [table name]

ADD [Column Name] [Datatype] SPARSE NULL ;
GO

Example:

ALTER TABLE dbo.table1

ADD Widecolumn1 varchar(10) SPARSE NULL ;
GO

What I need to know while using sparse column?
Please refer to http://msdn.microsoft.com/en-us/library/cc280604.aspx for details. However below tips are useful for immediate references:

1. geography, text, geometry, timestamp, image, user-defined data types, ntext cannot be used as a sparse column data type.
2. SELECT…INTO statement does not copy over the sparse column property into a new table.
3. A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties
4. A sparse column cannot have a default value or cannot be bound to a rule.
5. A sparse column cannot be part of a clustered index or a unique primary key index.
6. Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE

How to use data (read/write/alter) from a wide table?

You can use traditional statements to read/write/alter data in a wide table. However you should prefer using column set for data modification or retrieval for all sparse columns in a wide table instead of using traditional statements.

 

 

 

 

 

 

 Please refer to http://msdn.microsoft.com/en-us/library/cc280521.aspx for details on using column set.

SQL 2012 on Windows Core Edition

One good reason for moving to SQL 2012 will be it’s capability to run on Server Core Editions.

As core servers need less mainteaince and offer reduced attack surface, this will be a direct benefit on SQL Server 2012 also if it the instance is hosted on a core server. Basically all the advantegs of running a server core will be passed to SQL Server too.

For more details on advantages of running a core server please refer to http://msdn.microsoft.com/en-us/library/ee391628(v=vs.85).aspx

For details on installing SQL 2012 on a server core please refer to http://msdn.microsoft.com/en-us/library/hh231669(v=sql.110).aspx

Last Backup Time of All Databases

SELECTdb.Name AS DatabaseName,
MAX
(bus.backup_finish_date) AS LastBackUpTime
FROM
sys.sysdatabases db
LEFT
OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP
BY db.Name
order
by LastBackUpTime asc

New Licensing Model of SQL Server 2012 (Denali)

Denali is now formally announced as SQL Server 2012 and will be released in 2012.

SQL 2012 has many reasons for companies to upgrade but it will come with a major catch! “Cost Increase”.

(Although this is first major price hike of SQL Server since SQL 2005. So I guess this hike is not unfair. There was a minor hike for 2008 R2 also. Please refer to

http://tonymackelworth.wordpress.com/2010/04/24/pricing-increase-sql-server/ for details on the same.)

Here are some major changes in SQL Server 2012’s licensing model which will have impact on budget:

1. SQL Server license is now “Per Core” based. For example, if you have a server running with 2 quad- core processor processors, you will need to license 8 cores (4*2=8) instead of licensing 2 physical processors.

2. Enterprise edition will no more support CAL based license. That means you will need to migrate licensing of all Enterprise edition instances to “per core “licensing model even if your present instance is licensed on CAL.

3. Standard Edition will support CAL and core based licensing.

4. Business Intelligence Editions is a new edition for SQL 2012. It will support only CAL based licensing.

Impact on budget:

SQL 2012 is going to be more expensive compared to previous versions.

Example of price change for Level A licensing agreements:

Business Intelligence Editions -> $7026/Server (Same as Enterprise Edition)
Standard Editions -> $734/Server (No cost increase)
CALs -> $207/CAL ->  (This is a $45 increase/CAL)

Let us use below possible scenarios to analyze the situations:
Possibility 1: You have a Standard Server with 100 CAL. Compared to today’s cost, you will spend $4500 ($45*100) more for CAL licensing.

Possibility 2: You have an Enterprise Server with 100 CAL. For moving to SQL 2012, you need to move to per core license.

How your SA (Software Assurance) program can save your budget:

Microsoft did not forget old customers.If you have valid software assurance (SA) for your SQL Servers, you can minimize the impact on your budget. Contact with your Microsoft Reseller or Rep as soon as possible to discuss about your options. Generally if you complete the upgrade to SQL 2012 while you have a valid SA and within a prescribed time (which can be decided with you and your MS Rep/re-seller) then you can get up to 4 free core licenses per processor.

For example, if you have a SQL Enterprise Edition running on 2 physical quad core processors, then you do not pay anything extra as long as you have your existing edition licensed for 2 processors. Depending on your relationship with Microsoft, you may actually get all the required number of core licenses free even if you are running with more powerful processors.

That is, if you are having 8 core/processor in the above example, Microsoft may actually license your 16 cores for free. (8 cores * 2 processors = 16 core license required). This can save a good amount.

You should start discussing with your MS rep/reseller to discuss how to move from CAL based license to per core based license (if applicable) and how to minimize impact of the price impact of CAL. You may have to pay the price difference per CAL depending on your relationship with Microsoft.

Recommended Plan of action to minimize impact on budget:

If you are DBA or the person involved with licensing, you should be active now to minimize impact from this change. I recommend taking below steps as soon as you can:

Do an inventory (or revalidate it) of your SQL Server and licenses and engage with MS Rep to figure out best possible option for you.

Although SQL 2012 comes with a higher price tag that does not mean that your budget will be seriously impacted depending on your relationship with Microsoft. So consider to prioritize your SQL 2012 migrations to take full benefits of SA. (Per my best knowledge, we will get around 36 months after the release of 2012 to maximize the opportunities to avoid cost increase).

SQL 2012 Enterprise Edition comes with true unlimited virtualization. Consider exploring this option for future expansion if you are running data center editions of Windows.

Find port number of a SQL instance using TSQL

DECLARE @portNumber varchar(20), @key varchar(100)

if charindex('\',@@servername,0) <>0
begin
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' +@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
end
else
begin
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key, @value_name='Tcpport', @value=@portNumber OUTPUT
SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@portNumber)

How to kill a negative SPID (like SPID -2) in SQL Server?

What to do next when you find SPID -2 in SQL Server

 Error: Process ID -2 is not a valid process ID
This article applies to:
 SQL Server 2000
 SQL Server 2005
 SQL Server 2008

You’ve been performing an investigation in SQL Server into a performance issue. Maybe some database blocking issue and you’ve done the usual things and you’ve just run an sp_who or sp_who2. Or perhaps you’ve been browsing the error log for potential problems and spotted an odd error message. But fundamentally, what you see is a SPID with a negative number. Specifically, SPID=-2. And it’s at the top of a blocking chain with a dozen other transactions behind it trying to acquire resource. Until it’s killed, nothing will happen.

Using KILL -2 Should Do It

So you do what you always do in these situations. Wait for it to complete, or more usually, because there’s an issue, you make a call on it and kill the SPID:
KILL -2

 And what comes back is:

Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

I’ll give you the fix first, you’re probably desperate for it (and it doesn’t involve restarting SQL Server). Then I’ll explain why:

Run the following (you’ll need to have a SQL Server login with sysadmin or processadmin privileges to do this):

select req_transactionUOW
 from master..syslockinfo
 where req_spid = -2

This will return a 32 digit UOW number which looks like a GUID. Something like ‘DEF12078-0199-1212-B810-CD46A73F2498’

Copy this into your query window and run:
KILL ‘DEF12078-0199-1212-B810-CD46A73F2498’

Run sp_who/sp_who2 again and you will probably see that the offending SPID has disappeared. If it hasn’t, it’s probably still in rollback and you’ll have to wait, but by nature this kind of SPID is usually pretty quick to roll back and terminate.