SQL and SQL only

File Group Backups - Intro

2012-01-13T00:37:00.000-08:00

What is File Group backup?

Backing up a portion of a database, say a File Group is termed as Filegroup backup.
If you are wondering what are filegroups, then in short a Database's data files can be made of multiple files or groups of files. For more info on File Groups read here

When File Group backups are useful ?
Assume you have very large database with few hundred GBs or a few Terabytes. The database is divided into multiple filegroups, with recently loaded data in one file group and older data in other filegroups. For example, you have a database which maintains a shop's order/transaction details. Assume that the database is designed to have each year's transaction at one filegroup. Then, instead of backing up the entire database, it would save lot of disk space, if one backup's up the current year's file group alone.

How to take file group backups ?

The Screenshot shows how to take file group backup. Fairly straight forward.

What are the advantages of file group backups ?
1) Saves lot of space as you backup only a portion of the backup.
2) Can bring the database online partially and at a faster pace. You can restore only your highest priority filegroup first, bring it online while filegroups havent been restored.
3) If a table or particular filegroup is corrupted then One can restore the filegroup seperately.

Requirements
Any backup strategy is said to work only when one can successfully recover the database. With filegroup backups, there is one basic principle. Each filegroups that is online should be consistent with the rest of the filegroups in the database. Also, the primary filegroup should be restored first for the database to be partially online.

To explain bit more, assume you have a database 'DB' with filegroups FG1,FG2,FG3. All are read write file groups.FG1 is the primary file group.You can bring the database online partially with either of these

* File group backups of FG1 alone
* File group backups of FG1 + FG2
* File group backups of FG1 + FG3
* File group backup of FG1 + FG2 + FG3 ( this becomes completely online )

However one should note that FG2/FG3 backup set should have the same Restoration point as FG1. Restoration point is the time upto which backups where taken for a file/filegroup.

Assume one has taken full backup of a database at 1 PM and transaction log backups at 2PM and 3PM. After that there were no backups taken for the database. Then the restoration point is termed to be 3PM. In other words, the time upto which you are restoring a backup is termed as restoration point.

So in our case, one CANT bring the database partially ( excluding FG1 alone ) online with

* FG1 file group backup taken on 10th Jan 9 PM
* FG2 file group backup taken on 9th Jan 9 PM
* FG3 file group backup taken on 8th Jan 9 PM

Attempts to restore with these 3 backups alone will fail as FG3 contains transactions upto 8th Jan night, FG2 upto 9th night and FG1 upto 10th night.

What CAN work is

* FG1 file group backup taken on 10th Jan 9 PM
* FG2 file group backup taken on 9th Jan 9 PM
* FG3 file group backup taken on 8th Jan 9 PM
* Additional T-Log backups from 8th Jan 9 PM to 10th Jan 9 PM.

T-Log backups from 8th Jan 9 PM to 10th Jan 9 PM contain all the transactions till
10th Jan 9 PM and upon restoration we can bring FG1,FG2,FG3 to the same restoration point.

In short the two most important principles for filegroup backups are

1) Primary Filegroup should be restored first.
2) All the Filegroups should have the same restoration point.

The table below shows the recovery models and Modes at which filegroup backups are useful.

Recover model	Read only	Strategy
Full	No	Full FG backups + Differential + T-log backups
Simple	No	Doesn't work
Full	Yes	Full FG backups for Read write + T-Log backups
Simple	Yes	Full FG backups

On the upcoming posts, I will be explaining various backup strategies and restoration scenarios in detail.

Log file size after backup and restore

2011-11-01T02:47:00.000-07:00

Quick summary of the post:

A restoration of a full database backup retains the log file size before restoration.

Now for the details :

Consider a large database that you want to move from one server to another. Assume that the log file of the source database is huge. For taking a backup, the size of the log file doesn't matter as a backup operation always backs up only the used pages of database.After restoration, the restored database's log file size is same as the original database, even though the backup file used for restoration
is much smaller. If one has a space constraint in the destination server, then its better to shrink the log before taking the backup of the original
database.

Let us take the sample database dbadb . The database size is 107 MB with data file size being 16 MB and log file size being 91 MB. A full backup file size is only 3.2 MB

Database size

Full Backup size

A restore of the backup will create a database again at the original size of 107 MB with data and log file sizes being 16 MB and 91 MB respectively.

Restore of Database

Database size of restored database

So, if your destination server doesn't have enough space, then ensure your log file size is small before taking the full backup.

Backup/Restore vs Detach & Attach

2011-10-04T01:16:00.000-07:00

You want to move a database from one server to another. There are two options to do that.

1) Detach/Attach: To Detach the database from source server and copy the Data and log file ( MDF and LDF ) of the database and attach it in the destination server

2) Backup/Restore: Perform a SQL Backup of the database and move the
backup file to destination server and Restore the database in destination server.
A simple comparison of both the methods.

Detach/Attach	Backup and Restore
Detach/Attach is a offline operation. Source database will be inactive when you perform detach and attach operation	Source database can be accessed as it is a online operation
Detaching and Attaching the database happens instantly irrespective of the size of the database.Time taken in migrating the database is same as the time taken for copying the data and log files from one server to another	Backing up a database can take considerabale amount of time ranging from few minutes to many hours depending upon the database. Restore of a database on a average takes about 3 times of backup time. So, total time taken in migrating the database would be backup time + restore time + time taken to move the backup file from one server to another
Sometimes, if the Log file size is huge then one needs to copy large amount of data over the network	Backup file contains only used data pages in data file and hence the size of the backup file would not include the size of data log file
Sometimes, if the Log file size is huge then one needs to copy large amount of data over the network	Backup file contains only used data pages in data file and hence the size of the backup file would not include the size of data log file
If the data file is fragmented/ If the Data file has lots of free space with in the file ( allocated but unused ), then copying the data file would mean carrying additional bytes of data though the they are unused.	Backup copies only used data pages of the data file. So, the size of the backup is always close to the size of the size used with in the data file.
Detach / Attach is not recorded in any table in msdb database. so one has no record of who detached/atatched who or when was it done, what were the files detached, what size or where the files are stored etc.	Backup and restore operation details are always stored in msdb database tables with information like size,date,location,type of backup / restore.
Advanced options like mirrored backup/ partial backup/ compressed backup/ backup to tape/ point in time recovery are not available	All advanced options are available

Detach and attach method is useful when one wants move the database fast, without caring much about the availaiblity of source server.Backup and Restore is certainly a much graceful way to do the same.

Reading SQL Error log

2011-08-25T02:17:00.000-07:00

SQL Error log can be read from SQL Server's management studio. However, Management studio is too slow and definitely not the greatest way of taking a quick look at SQL Error log. Sp_readerrorlog is definitely a much better command which can help us read a error log must faster way. Also , the script below Dumps the read error log into a temporary table. Once dumped one can use different kinds of filters as per our needs.

The script below loads error log into a temporary table, filters for a particular date range, removes error log entries for backup, and searches only for genuine error on the error log.

CREATE TABLE #error_log_dt
  (
     logdate     DATETIME,
     processinfo VARCHAR(30),
     text_data   VARCHAR(MAX)
  )

INSERT INTO #error_log_dt
EXEC Sp_readerrorlog

SELECT *
FROM   #error_log_dt
WHERE  logdate BETWEEN '20110815' AND '20110821'
       AND processinfo != 'backup'
       AND text_data LIKE '%error%'

DROP TABLE #error_log_dt

ALTER TABLE - Adding Column - Column Order Impact

2011-06-25T20:22:00.000-07:00

Most of us would have altered a table structre by adding a column to the table. When an ALTER table script is used to add the column to the table, the column is placed on the last of the table. For inserting a column, in the middle of the table one needs to use the SQL Server Management Studio ( SSMS ).ie., Right click on the table and pick design table and then proceed to add a column. This post will deal with the significance of column order while adding a column to the table.

Consider the table 'sample'. The table contains about 800 rows with a size of 6 MB. Relatively small by a normal database standards.

Let us add column in the middle using SSMS as shown below. Once we click on save button to save the column addition, the operation completes immediateley.

Let me add a few more rows into the table. Now the table contains over 200K rows and the size of the table is 1.6 GB.

Now let me add a column to the middle of the table using SSMS as done proviously. Now the operation takes much much longer. Just in case if you face timeout error refer here.

Now the operation takes few hours to complete and the new column will be inserted between two columns as shown below.

Let us add one more column at the last of the table ( not in between the columns as doen previously). Refer to picture below.

ALTER TABLE sample ADD col3_int CHAR(5)

Now the operation takes in 0 seconds to complete. The points to note are provided below.

* When we add a column to the middle of the table using SSMS, when the number of rows are higher, it consumes a longer execution time and resource.

However, when the number of rows are lesser it doesnt consume much of time and CPU/IO resource. This aspect is to be handled carefully where a DBA can fall for the trap if overlooked.

Assume, DBA is planning to perform a small column addition on the production server. DBA has already tested in staging and it was over in few seconds. DBA assumes that the operation is going to take a few seconds in production and plans accordingly. If the number of rows are higher in production, the DBA can be taken for ride and it can be different ball game all together.

So lesson to be learnt is Dont underestimate any table modification prepations and make sure to check size/# of rows on production before deployment.

* When the column was added to the end of the table ( without caring about position of the column ) using T-SQL script ( ALTER TABLE coomand ),it completed immediately without consuming much of time and resource, though the number of rows were very high.

Lesson learnt is insertion of a column to the middle ( or rather between two other columns ) of the table should not be done, unless there is a strong reason to do so. If there is no strong reason, then always add the column to the end of the table using ALTER TABLE script as they take much much lesser time to execute.

* Use scripts instead of SSMS GUI especially while performing table strucutre modifications or DDL operations.

We will take a much closer look in the next post exploring why such a behavior is observed.

Altering Table structure - SSMS - Timeout Expired

2011-06-17T19:42:00.000-07:00

Altering a table using SQL Server Management Studio ( SSMS ) can be done by right clicking on the table and by picking the design table as shown below.

While adding a column, especially for a huge table, then management studio prompts saying changing the data can consume lot of resources and time as shown below.

After clicking 'yes', if the alter table takes longer than 30 secs then the alter table fails with the error message 'Time out expired' as shown below.

The error can be avoided by changing the default setting in SSMS as shown below. Goto Tools->Options->Tables and Database Designer and set the option Transaction time out after to 1800 seconds from default 30 seconds . The default setting is shown below.

Database Owner - Error Message

2011-06-12T02:42:00.000-07:00

Sometimes, a simple task that as a DBA you do day in and day out,
can throw wierd errors.

You right click on a database and click on Properties to check
the Database Size/ Database path. Suddenly, a error message as shown
below is thrown at you.

The error message says the problem is with the database owner. When the database owner is removed ( perhaps the login was dropped ) , such a error message is thrown.The problem can be rectified by using the command

EXEC MASTER.dbo.Sp_changedbowner 'sa'
GO

The command sets database owner as dbo and solves the problem. Now we can comfortably view database property from SSMS as usual.

Please note that sp_changedbowner command is marked for depcreation and users are recommended to use ALTER AUTHORIZATRION command.syntax is provided below.

ALTER AUTHORIZATION ON DATABASE::dbname TO principal;

For more details on AUTHORIZATION refer here

I am back

2011-06-11T19:55:00.000-07:00

I am back again!!!. It has been ages since I posted.An important event in my life kept me away from blogging. Now I am happily married to Sharadha. :) Till date, I havent posted a photograph of mine in this blog. Today is certainly a good day to that.

So, from now on the blog will be active as it was before and special thanks to all the readers for visiting even when it was not frequently updated.

Finding last database growth date and time

2011-01-04T17:10:00.000-08:00

A file growth operation on a database server is a extremely expensive operation. Many Performance problems correlate to a Data or log file growth event on the database. As a result its extremley important for a DBA, to have a way to check the time of Data or Log file growth occured earlier. A good way of finding it will be thro SQL default trace. Refer to the following query

DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC

The script provided above will list the databases that grew recently along with their time of growth. The script pulls these details from SQL Server''s default trace. Default trace is a light weight trace running at the background by default capturing many important events. Few of them are listed below.

Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings

SQL default trace can write upto 5 files with each file with a maximum size 0f 20 MB.
So maximum log of 100 MB will be generated. So, the amount of time SQL Trace retains the log details depends on the amount of activity on the server.One can change the same script to view other events and get other useful info like 'who altered that object?' , 'Who granted the rights to a particular table' and so on.

For more details on default trace refer here and here and here

Server Side trace

2010-12-24T01:30:00.000-08:00

Most of us are aware of tracing queries using SQL profiler. But an effective way of performing tracing is using a server side trace.

Whats special about server side trace?

1) Server Side trace uses much less resources than SQL Profiler.

2) Server side trace runs within the server which means the dependency on client tool is removed.In other words, when you run a profiler from a client machine, once you close the profiler, the SQL trace on the server stops. for ex: if the DBA is running a trace on production server from his desktop, then if the DBA's machine is restarted then the trace automatically stops. With a Server side trace as long as server is up the trace will be running and it doesnt depend on any client machine or tool.

3) SQL Profiler tool, while running, consumes lots of space on the C: drive ( or on the drive executables are installed ) by writing temporary files to C:\...\..\ temp folder. Temporoary files are cleared only by stopping the profiler, which would mean stopping the trace. There are enough articles on the net for Server Side traces. Please refer here and here. These links provide a fairly comprehensive expalantion on server side traces.

In short they say

1) configure the trace using profiler

2) Use the File and Export option on SQL Profiler to export the script of the trace.

3) Make the change on sp_trace_create parameter to 2 so that files roll over after the first trace file is filled up. If not the trace stops after the first trc file is full.

4) Execute it from SSMS to get the server side trace started.

5) Use the following functions to administer the trace

1) fn_trace_getinfo(default) or select * from sys.traces - to check trace status
2) sp_trace_setstatus - To start, stop and close a thread
3) fn_trace_gettable - To get the results of the trace files into a table.

Perfmon counters list - Quest poster

2010-12-08T18:17:00.000-08:00

Stumbled upon this PDF from quest which lists the important Perfmon counters and their acceptable values. A must print and stick poster which can be very very handy indeed. Saves so much of time and effort involved in reading pages of documents to know the important counters and correct value for each of them. Superb stuff. Thanks Quest !!!

Lock escalation : SQL Server 2008

2010-11-29T02:39:00.000-08:00

Lock escalation is a event which occurs when SQL Server decides to upgrade a lock at a lower level hierarchy to a lock to a table level lock., In other words, when a particular query obtains a large number of row level locks/ page level locks, SQL Server decides that instead of creating and granting number of row level/page level locks, it is effective to grant a single table level lock. Or to be precise,
SQL Server upgrades the row/page level locks to table level locks. The above process is termed as lock escalation.

Lock escalation is good, as it reduces the overhead in maintaining a large number of smaller level locks. A lock structure is about occupies about 100 bytes of memory and too many locks can cause a memory pressure.Similarly applying,granting , and releasing locks for each row or page is a resource consuming processes which can be reduced by lock escalation. However, Lock escalation also reduces concurrency. ie, If a query causes lock escalation, then the query obtains a full table level lock, and another query attempting to access the table will have to wait till the first query releases the lock.

How SQL Server decides when to escalate lock ?

* When a query consumes more than 5000 locks per index / heap.
* When the lock monitor consumes more than 40% of the static memory or non AWE alloted memory.

So Let us quickly see lock escalation in action. Consider the following query

SET ROWCOUNT 4990

GO

BEGIN TRAN

UPDATE orders
SET    order_description = order_description + ' '
--Rollback

The orders table has a clustered index. Row level locks will be taken on the index keys. SET ROWCOUNT ensures that only 4990 rows are updated by the query. I am leaving the transaction open ( without committing or rolling back ) , so that we can see the number of locks held by the query.

Fire the following query to check the locks held by the above script. The query lists the count of locks for each lock type and object. Note that the session id for the above script on my machine was 53. So filtering by the same.

SELECT spid,
       COUNT(*),
       request_mode,
       [resource_associated_entity_id],
       sys.dm_tran_locks.resource_type AS object_type,
       Db_name(sysprocesses.dbid)      AS dbname
FROM   sys.dm_tran_locks,
       sys.sysprocesses
       OUTER APPLY Fn_get_sql(sql_handle)
WHERE  spid = 53
       AND sys.dm_tran_locks.request_session_id = 53
       AND sys.dm_tran_locks.resource_type IN ( 'page', 'key', 'object' )
       AND Db_name(sysprocesses.dbid) = 'dbadb'
GROUP  BY spid,
          [resource_associated_entity_id],
          request_mode,
          sys.dm_tran_locks.resource_type,
          Db_name(sysprocesses.dbid)

As one may notice, we can find 4990 key locks / row level locks. Let us rollback transaction and modify the script to use 5000 or more locks.

SET ROWCOUNT 5000

GO
BEGIN TRAN

UPDATE orders
SET    order_description = order_description + ' '
--Rollback

Now let us fire the same query on sys.dm_tran_locks. we obtain a single exclusive lock on the table/object. There are no key or row level locks as SQL Server as per its rule has escalated the row level locks to a table level lock.

SQL Server 2005 had a server wide setting to disable lock escalations. On SQL Server 2005, when the trace flag 1211/1224 are set, no query is allowed to escalate locks on the entire server. Ideally, we would like to have it as a object/table level setting which was provided by SQL Server 2008. SQL Server 2008 allows one to disable lock escalations at table/ partition levels.

Consider the following command in SQL 2k8

ALTER TABLE orders SET CONSTRAINT (LOCK_ESCALATION = DISABLE )
GO

The ALTER TABLE command's LOCK_ESCALATION property accepts three values.

* Disable -> Disables lock escalation ( Except a few exceptions . Refer Books online for details )
* Table -> Allows SQL Server to escalate to table level. That is the default setting.
* Auto -> Escalation will be partition level if the table is partitioned. Else escalation is always up to table level.

Let us rollback the open transaction created earlier and run the ALTER TABLE command posted above to disable lock escalations. Now let us run the same script to update 5000 records again and see if lock escalation has actually occurred.

As you may now notice, for the same 5000 rows, there is no lock escalation occuring this time as we have disabled it using the ALTER TABLE command. The picture shows 5000 key/row locks which is not possible at the default setting of lock escalation.

The intention behind this post was to introduce lock escalation, show how it works and also explain the new option provided to change lock escalation setting SQL Server 2008. Upcoming posts, we will dive deeper into the topic and understand when and under what circumstances can we play with lock escalation setting.

Backup log Truncate_Only in SQL Server 2008

2010-11-16T03:22:00.000-08:00

BACKUP LOG <db_name> WITH truncate_only command, used for clearing the log file, is deprecated in SQL Server 2008. So this post will explain option available in SQL Server 2008 for truncating the log.

Step 1: Change the recovery model to Simple

USE [master]
GO
ALTER DATABASE [dbadb]
SET recovery simple WITH no_wait
GO

Step 2: Issue a checkpoint
One can issue a checkpoint using the following command.

CHECKPOINT
GO

Checkpoint process writes all the dirty pages in the memory to disk. On a simple recovery mode, the checkpoint process clears the inactive portion of the transaction log.

Step 3: Shrink the log file

USE dbadb
GO
DBCC shrinkfile(2, 2, truncateonly)

Shrinking the log file with a truncateonly option clears the unused space at the end of the log file. First parameter of the Shrinkfile takes the filed id within the database. Mostly the fileid of the log file is 2. You may verify the same by firing a query on sysfiles.

Step 4: Change the recovery model back to full/bulk logged
Change the recovery model to the recovery model originally ( full/bulk logged ) used by the database.

USE [master]
GO
ALTER DATABASE [dbadb]
SET recovery FULL WITH no_wait
GO

After these steps the log file size should have reduced.

The intention behind this post is not to encourage truncating the log files. Use the method explained, only when you are running short of disk space because of a log file growth. Note that, Just like truncating log files, Changing the recovery model also disturbs the log chain. After clearing the log using the above method, you need to either run a full backup/Differential backup to keep your log chain intact for any recovery.

Just a quick demo to show that the log chain breaks if you change the recovery model.

The database whose log file we will be clearing is dbadb. Log file size 643 MB as shown below.

After executing the scripts mentioned above, the log file size is 2 MB as shown below.

The log chain breaks after changing the recovery model. When log chain breaks, subsequent transaction log backups start failing as shown below.

Transaction log backups will be successful only after the execution of full or differential backup.

PS: Pardon me for a SQL 2k8 post, when the whole world is going crazy about
SQL Denali :)

Finding CPU Pressure - dm_os_schedulers DMV

2010-11-09T15:57:00.000-08:00

The last post dealt with checking CPU pressure using wait_stats DMV. But, to get a complete picture of CPU Pressure, the script provided in the previous post alone wouldn't suffice. We need the help of additional DMV
sys.dm_os_schedulers.

Why we need the help of sys.dm_os_schedulers?
As already mentioned, Waitstats DMV captures waiting time for a group of wait types. WaitStats DMV works in the following way. WaitStats checks whether any processes waits at any of the defined wait types. If yes, then WaitStats tracks resource wait time experienced by the process. After the resource wait is over, waitstats tracks the signal wait/CPU wait. Once, both the waits are completed, the waitstats reflect in the DMV.

There have been scenarios where a process doesn't experience any resource wait and just has a CPU wait/Signal wait. In such a case, the waiting time misses the wait stats, and wait stats doesn't reflect the CPU pressure experienced at all. In other words, if a process doesn't wait for any resource and directly gets
into the CPU queue and spends long time only in runnable state, then
sys.dm_os_wait_stats doesn't reflect it. The Same scenario is explained in detail by MVP Linchi Shea over here.

In such a scenario, one can use sys.dm_os_schedulers to detect CPU pressure.
Consider the following query.

SELECT scheduler_id,
       cpu_id,
       current_tasks_count,
       runnable_tasks_count,
       current_workers_count,
       active_workers_count,
       work_queue_count
FROM   sys.dm_os_schedulers
WHERE  scheduler_id < 255;

Simple and straight forward query on sys.dm_os_schedulers DMV. Returns one row per CPU. Scheduler_id < 255 filters out schedulers used by system processes. Detailed explanation on the DMV can be obtained from here. Most important column is runnable_tasks_count which indicates the number of tasks that are waiting
on the runnable queue. Once a process moves out of the runnable state into running state, the value of the column reduces by one. So, the DMV always indicates the current state of the server and the runnable queue.dm_os_schedulers doesnt hold historical data since last restart unlike waitstats dmv. So, this needs to
polled lot more frequently, perhaps every 5 minutes if necessary. Any non zero value noted on runnable_tasks_count is a indication of CPU pressure and requires close monitoring.

To sum it up, CPU monitoring for SQL Server would include three parts.

* Directly checking CPU using sys.os_ring_buffers dmv as explained here.
* Checking waitstats dmv as explained here
* checking sys.os_schedulers as explained in this post.

Finding CPU Pressure using wait_stats DMV

2010-10-31T02:25:00.000-07:00

CPU Pressure forms vital part in performance monitoring and sys.dm_os_wait_stats is a good place to check. Some background info before we look at the DMV.

Waiting time for a SQL Process is divided into two. They are Resource waits and signal waits( CPU waits ). When a process/query is submitted to SQL Server, SQL Server checks whether all resources required for the process are available.Resources would include things like IO, Network, Memory, Locks, Latch waits etc. For Ex: If a process is waiting for another process to release its lock, then it becomes a resource wait because of locks. If a process wants to write something to a disk but its has to wait for some other IO activity to complete then its a resource wait because of IO. So all these waiting time gets categorized as resource waits..

After a process grabs all the resources it requires, the process gets scheduled to run/executed by the Processor/ CPU. So, it enters the CPU queue. If there are already many processes executing, then it has to wait to get the CPU. The time spent waiting for the CPU is termed as signal waits.

sys.dm_os_wait_stats DMV provides the cumulative sum of the waiting time experienced by all processes at each wait type. dm_os_wait_stats DMV is a SQL Server 2005/2008 equivalent of DBCC SQLPERF(waitstats). A lot more accurate explanation about the DMV is provided here . dm_os_wait_stats maintains these stats since last restart of the server or since the last execution DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

sys.dm_os_wait_stats provides about 485 wait types/wait events and waiting time spent at each wait type. Two interesting columns of this DMV are

* wait_time_ms
* signal_wait_time_ms

Wait_time_ms gives the total time spent at each wait type ( Resource + cpu wait ). signal_wait_time_ms specifies the time spent on waiting for CPU. Resource waittime can be found by wait_time_ms - signal_wait_time_ms.

SELECT SUM(signal_wait_time_ms) * 100 / SUM(wait_time_ms) AS
       cpu_pressure_percentage,
       SUM(signal_wait_time_ms)                           AS cpu_wait,
       SUM(wait_time_ms - signal_wait_time_ms)            AS resource_wait,
       SUM(wait_time_ms)                                  AS total_wait_time
FROM   sys.dm_os_wait_stats

Consider the query above. cpu_pressure_percentage provides percentage of time spent waiting for CPU, by finding the ratio of signal_time and total wait time. Microsoft recommends this value to be less than 25% for a normal system. Value any higher would indicate a system suffering likely from a CPU stress.
Most common causes for CPU stress/high signal waits can be

* Poor and ineffective query plans / missing indexes / table scans
* Too many Plan Recompliations
* Slow CPU and requires a upgrade

As a production DBA, its a good idea to collect the resultset dm_os_wait_stats DMV once few hours or twice or thrice a day and perhaps analyze the results to notice any surge in wait time. This can be one proactive approach to detect potential major problems.

Backup path

2010-10-23T21:34:00.000-07:00

Sometimes after taking a backup, one can forget the path where the backup was taken.
The simple script below provides the list of backups taken on the server, backup type ( log - l, Full -D..),backup start date and time, lsn numbers and the path of the backup. Simple and handy.

SELECT database_name,
       TYPE,
       backup_start_date,
       first_lsn,
       last_lsn,
       backupmediafamily.physical_device_name
FROM   msdb.dbo.backupset,
       msdb.dbo.backupmediafamily
WHERE  backupset.media_set_id = backupmediafamily.media_set_id
ORDER  BY backup_start_date DESC

Currently executing query - wait status

2010-10-19T02:38:00.000-07:00

Recently, I had a developer who came to my desk with a request.

' I started a execution of a script from a program. the program has been running for a long time. I don't know whether its executing or hanging. Can you check from database whether the script is running or hanging and if hanging at which statement it is hanging?. If possible please tell me why its hanging..'

Earlier in my blog, I posted the following script to check the currently running query.

SELECT spid,
       TEXT                       AS [query],
       Db_name(sysprocesses.dbid) AS dbname,
       cpu,
       memusage,
       status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name
FROM   sys.sysprocesses
       OUTER APPLY Fn_get_sql(sql_handle)
WHERE  spid > 50
ORDER  BY cpu DESC

The above query would give the stored procedure's name or starting statements of the script/batch and not the exact statement within the stored procedure or batch that is currently executing. To find that one should use sys.dm_exec_requests

SELECT session_id,
       request_id,
       Db_name(database_id),
       start_time,
       status,
       command,
       Substring(txt.TEXT, ( statement_start_offset / 2 ) + 1,
       ( ( CASE statement_end_offset
       WHEN -1 THEN Datalength(txt.TEXT)
       ELSE statement_end_offset
                                                                   END
           - statement_start_offset ) / 2 ) + 1) AS statement_text,
       wait_type,
       wait_time,
       blocking_session_id,
       percent_complete,
       cpu_time,
       reads,
       writes,
       logical_reads,
       row_count
FROM   sys.dm_exec_requests
       CROSS APPLY sys.Dm_exec_sql_text([sql_handle]) AS txt
WHERE  session_id <> @@SPID
       AND session_id > 50

The query above gives the currently executing statement within a batch. sys.dm_exec_requests provides the columns statement_start_offset ,statement_end_offset which specify the starting and ending positions of the currently executing query within a batch, in bytes. As 'text' column returned by dm_exec_sql_text is of nvarchar datatype, statement_start_offset/2 is required to get to the starting postion of the query. statement_end_offset returns -1 to indicate the end of the batch.So, statement_text column of the query answers the first part of the developer's request.

Now, for the next part , ie finding the status of the query ie.. running/hanging/sleeping etc

A user request can take the following major states.

> Sleeping : Query completed execution and has nothing to do. Connection
is still not closed.
> Running : Currently running/executing.
> Runnable: Can run but waiting for a processor. Implies waiting in
processor queue.
> Suspended: Waiting for some resource or event. Wait may be because of
locks, IO completion, Latch or Memory wait etc.

Sleeping requests are not shown in sys.dm_exec_requests. Runnable/Suspended state requests are the ones we need to watch out for. Runnable/Suspended states are not abnormal, as long as they dont stay at the same state for a long time.

Now for the third part..' Why it is hanging..'

The wait_type column on the resultset will return the reason why a request is waiting. To know the meaning of each wait type refer here

Popular few wait types are given below

ASYNC_IO_COMPLETION - Waiting for IO
CXPACKET - Wait beacuse of Parallelism.
LOGBUFFER - Waiting for the logbuffer to release some space in the memory.
LCK_M% - Waiting for lock to be released. Refer to Blocking_Session_id for finding the blocking process.

Other interesting columns returned by dm_exec_requests are wait_time returning time spent waiting in ms, percent_complete indicating progress of the query,
cpu_time,reads,writes,logical_reads and row_count. percent_complete mostly indicates the progress for select queries,backup process but not for DML operations.

So, a quick execution of the script and checking the wait state details can clearly
show the current progress/ status of the request.

SQL Server never stores the same column twice

2010-09-27T02:22:00.000-07:00

Consider the following table.

CREATE TABLE dbo.tbl
  (
     col1_pky      INT PRIMARY KEY,
     col2_nc_index INT
  )

CREATE INDEX nc_col2_col1
  ON dbo.tbl(col2_nc_index, col1_pky)

I have created a table dbo.tbl with the column col1_pky as primary key.
A composite Non Clustered index is created on col2_nc_index,col1_pky columns.As by definition every non clustered index contains the clustered index key.
So going by definition, the non-clustered index should contain the following

* col2_nc_index,col1_pky - Index definition
* col1_pky - Clustered Index key

col1_pky is supposed to repeat twice as its a part of the nonclustered index and also clustered index key. But, SQL Server avoids the same by not storing the same
column twice.

/* Data Generation Script */
DECLARE @col1 INT,
        @col2 INT

SET @col1 = 1
SET @col2 = 10000

WHILE @col1 < 10000
  BEGIN
      INSERT INTO tbl
      SELECT @col1,
             @col2

      SET @col1 = @col1 + 1
      SET @col2 = @col2 - 1
  END

As usual let us use DBCC IND / PAGE to check the same.
To check the root page

DBCC ind ( 'dbadb', 'tbl', -1)

Root page is found as 18684

Let us take a look at the contents of root page using DBCC PAGE

DBCC traceon(3604)
GO
DBCC page(dbadb, 1, 18684, 3)
GO

Note that col1_pky appears only once and doesnt appear twice. To confirm the same let us check the contents of a leaf level page.

DBCC page(dbadb, 1, 19044, 3)
GO

Again col1_pky is present only once. So, SQL Server always stores a column only once in a table.

Avoiding Explicit Permission - Execute as clause

2010-09-12T19:06:00.000-07:00

Providing direct access on tables for application user accounts is always a security concern. But, there are some scenarios which forces us to do the same. I previously discussed it in detail over here. SQL Server 2008/2005 provides a few options to avoid providing direct rights, even when the stored procedure involves dynamic sql, DDL, cross database reference etc. This post will explore one such option.

Execute AS option:
While creating stored procedures, we can specify EXECUTE AS clause to run the stored procedure under a different account. For example consider the following

CREATE PROCEDURE dbo.Rights_check
AS
  BEGIN
      DECLARE @txt NVARCHAR(100)
      SET @txt = 'Select * from databaselog'
      EXEC Sp_executesql @txt
  END
GO

Let us grant rights to account test1, which doesn't have direct access to 'databaselog' table.

GRANT EXEC ON dbo.rights_check TO test1
GO

Executing the 'dbo.rights_check' procedure from 'test1' account fails as test1 doesn't have direct access on 'databaselog' table.Using 'Execute As' clause can allow test1 to execute 'dbo.rights_check' procedure successfully without directly providing rights on the 'databaselog' table.

ALTER PROCEDURE dbo.Rights_check
WITH EXECUTE AS owner
AS
  BEGIN
      DECLARE @txt NVARCHAR(100)
      SET @txt = 'Select * from databaselog'
      EXEC Sp_executesql @txt
  END

I have just added the EXECUTE as OWNER clause on top which ensures that the stored procedure is executed in the context of owner of the stored procedure. Execution of the stored procedure using test1 is successful as expected.

The reason is, adding EXECUTE as OWNER allows any account which has execution rights on the stored procedure to execute under the security context of the owner of the stored procedure. This ensures that the executing account is allowed to obtain the special permissions only within the stored procedure and not outside the scope of it.

EXECUTE AS clause provides additional options like EXECUTE as 'user_name'. EXECUTE as 'user_name' allows one to execute under the context of particular user account instead of owner of the object.More details on the same can be read here.

EXECUTE AS clause effectively solves the security problems that arise when DDL statements, dynamic sql, cross database references are used.Digital Signatures, introduced in SQL Server 2005 also helps in addressing similar security problems which we will cover in the next post.

DBCC IND/ PAGE - Unique Non Clustered index structure

2010-09-06T21:06:00.000-07:00

I am back after a short break. Few personal and official commitments have kept me away from posting. Apologies.From now on I will be posting at usual pace.

Continuing from my series of posts on DBCC IND, DBCC PAGE, this post will deal with the structure of Unique Non clustered index.Refer to the following links for previous posts on the same topic

1. DBCC IND/PAGE intro - Refer here
2. DBCC IND/ PAGE - Non Clustered Index structure on a table with Unique/Non unique Clustered index - Refer here

Structure of a Unique Non clustered index is different from ordinary ( Non unique ) non clustered index. The difference is that the Clustered index columns,
which are normally a part of Non clustered index, are not present in the Non leaf nodes when the Non clustered index is unique.When a Non clustered index is
unique, the clustered index columns are stored only in the leaves of the Non clustered index.

A detailed explanation on the same is given by the legendary Kalen Deanley :) Who else in the planet can explain internals better than her?
So, for more details on the topic, refer to her article here.

Stored Procedures , Explicit Permissions and Security concerns

2010-08-17T07:06:00.000-07:00

Its always a good practice to have your code wrapped in stored procedures.
One of the reasons is Security. To explain a bit more, when one grants execution rights on a stored procedure to a user, the user gets the rights to perform all the operations ( Select/Insert/update/delete ) within the stored procedure.However, the same user cannot perform the operations outside the context of stored procedure.

Ex:
GRANT EXEC ON dbo.usp_stored_proc TO user1

Assume that the stored proc dbo.usp_stored_proc performs Select and update on table1, then user1 can perform these operations only while executing dbo.usp_stored_proc and not directly. In other words, user1 cannot bypass the stored procedure and directly perform a select/update on table1.

But there are a few operations, where one needs to explictly grant permission to a object inside a stored procedure.In other words, its not enough if we just grant EXEC rights on the stored procedure. Let me list down such scenarios.

1) Using Dynamic sql queries using sp_executesql / EXEC :

If your stored procedure is using Dynamic SQL using sp_executesql then the
objects accessed in the dynamic sql require explicit permissions.

For Example

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      DECLARE @dsql NVARCHAR(100)

      SET @dsql = ' Select * from table1 '

      EXEC Sp_executesql @dsql
  END

GO

For the above stored procedure, its not enough if we grant execution rights to
dbo.usp_stored_proc. In addition,one needs to grant select rights on 'table1' for the user executing stored procedure. Explicit grant is required because dynamic sql are always treated as separate batch outside the scope of the stored procedure.

2) Cross database reference

If you are accessing a table on another database, then one needs to explicitly grant rights.

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      SELECT empid,
             salary,
             increment,
             name
      FROM   hr_database.dbo.salary
      WHERE  username = 'clerk';
   .......

    ...........

   Some code

   ..........

   ...........


  END

GO

In the above stored procedure, the salary from database 'HR_database' is accessed. The user who calls the stored procedure should have rights on the HR_database.dbo.salary for the stored procedure to execute successfully.

Please note that the above scenario is true, when cross database ownership chaining is not enabled.If cross database ownership chaining is enabled, and if both the objects(dbo.usp_stored_proc and HR_database.dbo.salary;) belong to the same owner, then explicit permissions need not be granted.

3) While using linked servers

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      SELECT *
      FROM   linkedserver.DATABASE.dbo.table2;
  END

GO

On the above stored procedure, linked server is used to refer to a table in a remote server.In such a case, the login that maps the user ( executing the stored procedure ) to the remote server should have select rights on table2. For understanding login mappings on linked server refer here.

4) Using DDL statements

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      TRUNCATE TABLE dbo.t1;
  END

GO

If the stored procedure contains DDL statement like truncate,alter table, Create index then appropriate rights should be granted to caller of the stored procedure.Meagre execution rights on the stored procedure wouldnt suffice.

On all the four scenarios listed above, ideally one would want the caller of the stored procedure to use these extra permissions only while executing the stored procedure. At the rest of the time, we wouldnt want the caller to gain direct access on the table.But by granting the rights explicitly, the caller of the stored procedure gets additional rights to perform the above mentioned operations without executing the stored procedure. ie, anyone can use the calling account and connect to the database and perform a Select on a entire salary table on HR_database or truncate a table t1, without executing stored procedure. Obviously, this can be a serious security concern.

To prevent the same, there are a few excellent options in SQL Server 2005, SQL Server 2008 which will be discussed in the next post.

DBCC IND/ PAGE - Non Clustered Index structure on a table with Unique/Non unique Clustered index

2010-08-09T07:15:00.000-07:00

Continuing from the last post, let us analyze the structure of a non clustered index using DBCC IND/PAGE commands, when we have a clustered index on the table. To be specific, we will see the difference in structure of a Non Clustered index when we have unique clustered or non unique clustered index.

As already written earlier here, a Non clustered index will store clustered index key in its index.However, there is a small change in Non clustered index when the clustered index is defined as Non unique.When one searches using the Non clustered index , the clustered index key helps in reaching the actual row in the table. But, when the clustered index is not unique SQL Server adds a additional Unique identifier column along with the clustered index key on the non clustered index. We will see the same using DBCC IND and DBCC PAGE commands.

Table structure is provided below. Students table has 2 columns namely 'student_name','sid'.'sid' has a UNIQUE clustered index. student_name has a non clustered index.

CREATE TABLE [dbo].[students]
  (
     [student_name] [VARCHAR](50) NULL,
     [sid]          [INT] NULL
  )
ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [CIX_students_id]
  ON [dbo].[Students] ( [sid] ASC )

CREATE NONCLUSTERED INDEX [IX_students_name]
  ON [dbo].[Students] ( [student_name] ASC )

I have loaded about 100,001 rows on the table. Let us see the structure on Non clustered index [IX_students_name] using DBCC IND/PAGE command.

Following steps are involved in reading the structure of the index.

1) Finding the root page of the index.

Execute the following command.

DBCC ind ( dbadb, students, 2)
GO

The third parameter is the ID of Non Clustered index [IX_students_name] which is obtained from sysindexes table.So we see only the pages of [IX_students_name] in our result set.

The command will show all the pages in the index. To identify the root of the index , identify the row with the highest Indexlevel. Indexlevel 0 refers to leaf pages. In Students table, the highest Indexlevel is noted as 1( which means there are only two levels on the index ie root and leaves ) and the page number is 17080.

2) Checking the contents of the root page
For checking contents of the root page execute the following command.

DBCC traceon(3604)
DBCC page(dbadb, 1, 17080, 3)
GO

You would see the non clustered key and clustered index key ( sid ) column on the root page of [IX_students_name].But there wont be any Unique Identifier as the Clustered index is unique.

Non clustered index structure with a non unique clustered index:

Now let us check the structure of the Non clustered index when we have Non Unique clustered index.For that let us alter our clustered index to non unique cluster using the following command.I have just removed the UNIQUE keyword from the earlier script and recreated the clustered index using DROP_EXISTING = ON option.

CREATE CLUSTERED INDEX [CIX_students_id]
  ON [dbo].[Students] ( [sid] ASC )
  WITH ( drop_existing = ON) ON [PRIMARY]

After executing the above command, the clustered index is non unique.
Now let us again analyze the structure of Non clustered index.

1) Root of the index

DBCC ind ( dbadb, students, 2)
GO

Root page number is noted as 15992

2) Contents of the root page

DBCC traceon(3604)
DBCC page(dbadb, 1, 15992, 3)
GO

Picture of index page with Uniquifier

You would notice that a new column called 'Uniquifier' is added to the non clustered index's leaf page as the clustered index is not unique anymore. Uniquifier has NULL values as there are no duplicate rows in the table.

Let us introduce a duplicate value in the table using the following script.

SELECT *
FROM   students
WHERE  sid = 100000
GO
/* just to confirm that i have only one row. */
INSERT INTO students
SELECT *
FROM   students
WHERE  sid = 100000
/* Manually inserting a duplicate */
SELECT *
FROM   students
WHERE  sid = 100000
GO
/* Verifying that we have a duplicate */

Note that the row has a value E-100000 on student_name column

Locating a value with in the index using DBCC IND/ DBCC PAGE:

To see how uniquifier is used in Non clustered index ,we need to find the duplicate row on the Non Clustered index with the value 'E-100000'. To do the same let us follow the following steps.

1) Finding the root page:
We already know that root page id of [IX_students_name] is 15992

2) Identify the page in which E-100000 is residing

Fire the same DBCC page command used earlier

DBCC traceon(3604)
DBCC page(dbadb, 1, 15992, 3)
GO

The resultset of DBCC PAGE is quite similar to a telephone directory index page where key column(s) ( student_name ) is the name of the person we are searching for and ChildPageid column is the Pagenumber in the directory.

For example,observe the Picture of index page with Uniqufier posted above.The value A-19032 on the student_name column ( 2nd row ) indicates that page number 15929 ( child page id ) contains rows starting from A-19032 and just before 'A-28080'. So to find E_10000 we should scroll to the row that exceeds (alphabetically) E-10000 by just and then goto the previous row and pick up the corresponding childpageid.

Observe the first row that alphabetically exceeds E-100000.
Refer to the row before that which is likely to contain E-100000.ChildPageID gives the page number of the page linked to row. The Childpageid on the immediate previous row exceeding E-100000 is 8605. So Page number 8605 should contain E-100000.So, execute the following command.

DBCC page(dbadb, 1, 8605, 3)
GO

Notice two rows with 'E-100000' which reflect the duplicate row we inserted earlier.
Note the uniquiefier column for rows containing E-100000. They have values of 0,1 which will be used to identify the correct row in the clustered index.

So, the conclusion of this long post is if you have a Non unique clustered index,
then additional Uniquifier column will be added in the Non clustered index to locate the correct row.

DBCC IND, DBCC PAGE - Intro

2010-08-02T08:09:00.000-07:00

This post is a gentle intro into couple of useful undocumented commands which I will be using in the next couple of posts.

DBCC IND

DBCC IND command provides the list of pages used by the table or index. The command provides the page numbers used by the table along with previous page number,next page number. The command takes three parameters.

Syntax is provided below.

DBCC ind ( <database_name>, <table_name>, non clustered index_id*)

The third parameter can either be a Non Clustered index id ( provided by sys.indexes ) or 1,0,-1,-2. -1 provides complete information about all type of pages( in row data,row over flow data,IAM,all indexes ) associated with the table. The list of columns returned are provided below.

IndexID: Provides id of the index. 0 - for heap, 1 - clustered index.,Non
clustered ids > 2 .
PagePID : Page number
IAMFID : Fileid of the file containing the page ( refer sysfiles )
ObjectID : Objectid of the table used.
Iam_chain_type: Type of data stored ( in row data,row overflow etc )
PageType : 1 refers to Data page, 2 -> Index page,3 and 4 -> text pages
Indexlevel: 0 - refers to leaf. Highest value refers to root of an index.
NextPagePID,PrevPagePID : refers to next and previous page numbers.

Example:
The command provides the pages used by table named Bigtable in database dbadb.

DBCC ind(dbadb, bigtable, -1)

DBCC PAGE:
Next undocumented command we would be seeing is DBCC PAGE:
DBCC PAGE takes the page number as the input parameter and displays the content of the page.Its almost like opening database page with your hands and viewing the contents of the page.

Syntax:

DBCC page(<database_name>, <fileid>, <pagenumber>, <viewing_type>)

DBCC PAGE takes 4 parameters. They are database_name, fileid, pagenumber, viewing_type.Viewing_type parameter when passed a value 3 and displays the results in tabular format.If you are viewing a data page then the results are always in text format. For Index pages, when we pass the value 3 as parameter we get the results in a tabular format.DBCC PAGE command requires the trace flag 3604 to be turned on before its execution.

A sample call is when a Index page is viewed is provided below:
Note that the page number picked (9069) is a page from clustered index of the table
'Bigtable'. 'Bigtable' has a clustered index on a column named 'id' .

DBCC traceon(3604)
GO
DBCC page(dbadb, 1, 8176, 3)
GO

Useful columns returned are provided below:
Level : Index level
id(Key) : Actual column value on the index. The indexed column name suffixed with '(key)' becomes a part of a result set. If your index has 4 columns then 4 columns with the suffix '(key)' will be a part of your result set. In the above example the data/values on column 'id' present in the page 8176 are displayed.
ChildPageid: Pageid of the child page.

A sample call when a data page number is passed is shown below:

DBCC traceon(3604)
GO
DBCC page(dbadb, 1, 9069, 3)
GO

Bit cryptic to read the text format results. But anyways we will using it less compared to index page results.

What we intend to do with these two commands ?

These two commands help us understand index structures, they way pages have been allocated and linked in a much better way. DBCC IND and PAGE are the two commands with which we can really get our hands dirty while trying to understand index structures. In the next couple of posts, I will analyze index structures using these commands and provide some interesting inferences on how index structures are arranged internally.

References : As usual Kalen Deanley - SQL Server Internals :)

Disabling Primary Key/ Clustered index

2010-07-25T18:57:00.000-07:00

Most of us are aware that indexes and constraints can be disabled. This post will concentrate on what happens when a Clustered index or Primary key is disabled.

When a Clustered index is disabled , irrespective of whether it is a part of primary key or not, the entire table is placed offline. Placed offline essentially means that one cant issue any select/insert/update/delete on the table and also all the other indexes associated with the table are disabled. When a clustered primary key is disabled, in addition to the impact happening because of disabling clustered index, the foreign key constraints on the tables that are referencing the disabled primary key are disabled as well.But the most important part is when you enable back your primary key, the foreign key constraints still remain disabled. We will see a quick example.

I have a student table with sid column as clustered primary key.

CREATE TABLE [dbo].[Student]
  (
     [sid]          [INT] NOT NULL,
     [student_name] [VARCHAR](50) NULL,
     CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [sid] ASC )
  )
GO
CREATE INDEX ix_student_name
  ON [Student]([student_name])
GO

Student_marks table references student table. Script for the same is provided below.

CREATE TABLE [dbo].[student_marks]
  (
     [sid]          [INT] NOT NULL,
     [marks]        [INT] NULL,
     [subject_name] [VARCHAR](50) COLLATE sql_latin1_general_cp1_ci_as NOT NULL,
     [subject_id]   [INT] NOT NULL,
  )
GO
ALTER TABLE [dbo].[student_marks] ADD CONSTRAINT [FK_student_marks_Student]
FOREIGN KEY([sid]) REFERENCES [dbo].[Student] ([sid])
GO

Let us disable the primary key on the student table. It can be done by

ALTER INDEX pk_student ON dbo.student DISABLE
GO

Warning: Foreign key 'FK_student_marks_Student' on table 'student_marks' referencing table 'Student' was disabled as a result of disabling the index 'PK_Student'.
Warning: Index 'IX_student_name' on table 'Student' was disabled as a result of disabling the clustered index on the table.

We obtain a warning message saying that the Non Clustered Index on student table and the foreign key constraint on student_marks table are disabled.The same can be confirmed by querying sys.foreign_keys tabled. The is_disabled column with a value one indicates that the constraint is disabled.

SELECT Object_name(parent_object_id)     AS table_name,
       Object_name(referenced_object_id) AS referenced_pky_tbl_name,
       is_disabled,
       name                              AS constraint_name,
       type_desc
FROM   sys.foreign_keys
WHERE  Object_name(referenced_object_id) LIKE 'student'

Now let me enable the Primary key constraint back on student table.
The command used is provided below.

ALTER INDEX ALL ON dbo.student rebuild

Primary key constraint is enabled by using the Index Rebuild command. One can rebuild just the Clustered index to enable the primary key but that would not enable the other non clustered indexes on the table. ALTER INDEX ALL enables all the indexes that are present in the table and hence it is the recommended command if you have disabled your clustered primary key.

To prove that foreign key is not enabled,let me insert a row into student_marks table which violates the constraint.I execute the following script which effectively proves the same.

SELECT *
FROM   student
WHERE  sid = -1

GO /*-- To ensure that no row on student has sid = -1 */
INSERT INTO student_marks
            (sid,
             subject_id,
             subject_name,
             marks)
SELECT -1,
       1,
       'Tamil',
       60
GO

SELECT *
FROM   student_marks
WHERE  sid = -1

GO /* to confirm that insert was successful */
SELECT Object_name(parent_object_id)     AS table_name,
       Object_name(referenced_object_id) AS referenced_pky_tbl_name,
       is_disabled,
       name                              AS constraint_name,
       type_desc
FROM   sys.foreign_keys
WHERE  Object_name(referenced_object_id) LIKE 'student'
GO

To enable the constraint first delete the invalid row and then use Check constraint command.

DELETE student_marks
WHERE  sid = -1
GO

ALTER TABLE [dbo].[student_marks] WITH CHECK CHECK CONSTRAINT CONSTRAINT [FK_student_marks_Student]

So, the conclusion of this post is always remember to enable the foreign key constraints after you have disabled and enabled your primary key. Let me finish the post with a quick script to generate a script for enabling foreign key
constraints.

DECLARE @primary_key_tbl_name VARCHAR(500)

SET @primary_key_tbl_name = 'student'

SELECT 'ALTER TABLE [' + sys.schemas.name + '].[' +
       Object_name(parent_object_id) +
              '] WITH CHECK CHECK CONSTRAINT [' + sys.foreign_keys.name + ']' AS
       enable_fky
FROM   sys.foreign_keys,
       sys.schemas
WHERE  Object_name(referenced_object_id) LIKE @primary_key_tbl_name
       AND is_disabled = 1
       AND TYPE = 'F'
       AND sys.schemas.schema_id = sys.foreign_keys.schema_id
GO

Set the value of @primary_key_tbl_name to the table whose primary key you have disabled. Executed the script after you have disabled and enabled the primary key. The Script pulls the related foriegn keys to the table and generates the enable script for the same.

Number of Drives,Location and Raid levels

2010-07-19T02:09:00.000-07:00

When you are setting up your new server, One of the important tasks you have its to pick the right number of disks for correct purposes at the correct RAID level. This article briefly deals with the same.

SQL Server Data files: One needs atleast one dedicated drive for Data files. Ideally, for best performance one needs the disk to be have it Raid 10 as it involves random reads and random writes.

SQL Server Log files: Log files have more sequential reads and writes. To avoid any IO contention, one needs a dedicated drive at Raid 1.

SQL Server Backups: Backups are always to be kept on a drive different from data and log files so that they don't become a single point of failure. ie., When your data/ log file drive gets corrupted then your backups are also lost , if placed on the same drive. As Backups involve high writes RAID 1 / RAID 10 is recommended.

Location of Operating System: Operating system is ideally to be placed in C:\ drive and should always be kept away from data file drive, log file drive and backup drive.

Location of SQL Server executables: SQL Server's executables are similar
to operating system files and hence we are better off keeping them away from Data and log files.

Tempdb location: In general, tempdb involves heavy writes and ideally Raid 1 or Raid 10 is recommended.It is a luxury if you can afford a separate disk for tempdb. If your application heavily uses tempdb then you are strongly recommended to do so.

So, Ideally one atleast needs 3 dedicated disks for SQL Server with one each for Data file , Log files, Backups.Note that this doesn't include C drive required for Operating system + SQL Server's executable.Out of the 3 dedicated SQL Server, one is expected to be on RAID 10. I realize that RAID 10 is the most expensive but all good things in this world are expensive., aren't they :) The most popular raid level, RAID 5 is mostly good for reads and for file servers which don't change much. RAID 5 performs 50% slower than RAID 10.

References: As usual another cool Brad McGhee article from here.

Does IDENTITY_INSERT reset identity?

2010-07-14T19:59:00.000-07:00

Most of us are aware that if we want to force a value of our choice on a identity column then we use the command SET IDENTITY_INSERT table name ON . But does that reset the identity? What kind of impact does it have on the current identity is what we will see on this post.

Consider the following script.

CREATE TABLE test_table
  (
     auto_id  INT IDENTITY(1, 1),
     somedata VARCHAR(50)
  )
GO
/* Creating a table with a identity value */

INSERT INTO test_table
SELECT 'x'
GO 10

Note that GO followed by number executes the same query number of times. The above statement on a query analyzer/ssms inserts 10 times producing 10 rows on the test_table.

To confirm the current identity value let us use ident_current command

SELECT Ident_current('test_table')

As expected the current identity value is 10.
Let us push the value 50 into the table. It can be done by

SET identity_insert test_table ON
INSERT INTO test_table
            (auto_id,
             somedata)
SELECT 50,
       'x'
SET identity_insert test_table OFF

Now let us check the identity value again.

SELECT Ident_current('test_table')

The identity value has been reset to 50. To confirm the same, let us do a insert of a row and verify the value.

INSERT INTO test_table
SELECT 'x'
SELECT MAX(auto_id),
       Ident_current('test_table')
FROM   test_table

The new value inserted is 51 as shown in the picture.
So this effectively proves that SET IDENTITY_INSERT resets the value of the identity column.But wait.There is some more. Let us try to force another value, but this time lower than the current identity value 51.So let us force 25.

SET identity_insert test_table ON
INSERT INTO test_table
            (auto_id,
             somedata)
SELECT 25,
       'x'
SET identity_insert test_table OFF

Let us check the current seed.

SELECT MAX(auto_id),
       Ident_current('test_table')
FROM   test_table

The seed still remains at 51 as it was earlier. So, IDENTITY_INSERT hasnt reset the identity this time.

So, the conclusion is IDENTITY_INSERT resets the value if the value forced is higher than the current identity value.If the value forced is less than the current identity value then IDENTITY_INSERT doesnt reset the value. This behaviour helps when one needs to fill any gaps caused in the identity column because of delete statments.

What are the other commands that can affect identity property?
1) Truncate table resets the identity value seed set at the table creation. However, if the table contains any foriegn key contraints then truncate table doesnt work.

2) DBCC CHECKIDENT(table_name,reseed,new_seed_value):
Checkident can reseed the table's seed to new value whereas truncate always sets the identity value to the value set initially at the table creation.

All the options specified above namely identity_insert,truncate_table,DBCC checkident require sysadmin, db_owner or DDLadmin rights on the sql server/database.

Lock Monitoring - SQL Server 2005

2010-07-08T02:59:00.000-07:00

As mentioned in the previous post, the query provided below can be
use to check locking on sql server 2005. Script is Simple , but powerful :)

SELECT spid,
       TEXT                                            AS [query],
       request_mode,
       request_type                                    AS LOCK,
       request_status,
       sys.dm_tran_locks.resource_type                 AS object_type,
       Db_name(sysprocesses.dbid)                      AS dbname,
       cpu,
       memusage,
       physical_io,
       status                                          AS query_status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran,
       Db_name(sys.dm_tran_locks.resource_database_id) AS locked_database_name
FROM   sys.dm_tran_locks,
       sys.sysprocesses
       OUTER APPLY Fn_get_sql(sql_handle)
WHERE  spid > 50
       AND sys.dm_tran_locks.request_session_id = spid
ORDER  BY request_mode DESC

Lock monitoring - SQL Server 2000

2010-07-07T08:07:00.000-07:00

One of the important parts of performance monitoring is monitoring locks. Imagine a situation, where you have a query that just waits for long and
you want to find which query and connection is locking it. One option is to use sp_lock and check which connection holds exclusive lock and then check
use dbcc inputbuffer to find the actual query. Another option is to Activity monitor on the Enterprise manager. Both of these dont directly specify the query responsible for locking. The following script will list the queries along with the locks held by them.

CREATE TABLE #sp_lock_details
  (
     spid     INT,
     dbid     INT,
     objid    INT,
     indid    INT,
     TYPE     VARCHAR(100),
     resource VARCHAR(1000),
     mode     VARCHAR(10),
     status   VARCHAR(50)
  )

INSERT INTO #sp_lock_details
EXEC Sp_lock

DECLARE @id INT
DECLARE @max INT
DECLARE @spid INT
DECLARE @exec_qry VARCHAR(100)
DECLARE @monitored_dt DATETIME

SET @monitored_dt = Getdate()

CREATE TABLE [#sysproc]
  (
     id             INT IDENTITY(1, 1),
     [spid]         [SMALLINT] NOT NULL,
     [dbname]       [NVARCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NULL,
     [cpu]          [INT] NOT NULL,
     [memusage]     [INT] NOT NULL,
     [status]       [NCHAR](30) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [loginame]     [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [hostname]     [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [lastwaittype] [NCHAR](32) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [last_batch]   [DATETIME] NOT NULL,
     [cmd]          [NCHAR](16) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [program_name] [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     QUERY          VARCHAR(255),
     open_tran      INT
  )
ON [PRIMARY]

CREATE TABLE #dbccinfo
  (
     eventype  VARCHAR(255),
     param     INT,
     eventinfo VARCHAR(255)
  )

INSERT INTO #sysproc
            (spid,
             dbname,
             cpu,
             memusage,
             status,
             loginame,
             hostname,
             lastwaittype,
             last_batch,
             cmd,
             program_name,
             open_tran)
SELECT spid,
       Db_name(MASTER.dbo.sysprocesses.dbid) AS dbname,
       cpu,
       memusage,
       status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran
FROM   MASTER.dbo.sysprocesses
WHERE  spid > 50

SET @max = @@IDENTITY
SET @id = 1

WHILE ( @id <= @max )
  BEGIN
      SELECT @spid = spid
      FROM   #sysproc
      WHERE  id = @id

      SET @exec_qry = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(3), @spid) + ')'

      INSERT INTO #dbccinfo
      EXEC (@exec_qry)

      UPDATE sys
      SET    QUERY = eventinfo
      FROM   #sysproc sys,
             #dbccinfo
      WHERE  @id = id

      TRUNCATE TABLE #dbccinfo

      SET @id = @id + 1
  END

SELECT #sysproc.spid,
       QUERY,
       #sp_lock_details.mode          AS LOCK,
       TYPE                           AS object_type,
       dbname,
       cpu,
       memusage,
       #sysproc.status                AS query_status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran,
       Db_name(#sp_lock_details.dbid) AS locked_object_dbname,
       #sp_lock_details.indid         AS locked_obj_indid
FROM   #sysproc,
       #sp_lock_details
WHERE  #sp_lock_details.spid = #sysproc.spid
ORDER  BY #sp_lock_details.mode DESC

DROP TABLE #dbccinfo
DROP TABLE #sysproc
DROP TABLE #sp_lock_details

Fairly simple script. Logic is to get the sp_lock details along with sysprocesses details and fire a inputbuffer or each spid.The Script provides the query involved along with the details from sysprocesses and hence it covers all the info provided by Activity monitor and adds a valuable addition by giving the query as well.The script provides the results in descending order of lock mode, so
that 'X' the exclusive lock, which most of us are interested in comes first.

The Script for SQL Server 2005 is even more simpler, which I will post in the next post.

SQL Pass blog directory

2010-06-30T19:26:00.000-07:00

My blog has been listed in sql pass blog directory. Check it over here. No matter what, its a special feeling to see your blog and name listed along with the best in the industry ( though you are no where close to them ). Thanks SQL PASS.

Rebuild vs ReOrg

2010-06-28T02:15:00.000-07:00

Rebuild or Regorg, difference between Rebuild or Reorg is a fairly common topic on blogs and forums. Here is my take on it.As I am not good on writing tr and td html tags lemme list down the comparison as pointers instead of a tabular column.

Before starting the comparsions, just a small intro on fragmentation. I have written in detail on it over here. But still, a quick intro.
Database Fragmentation is of 2 types. Internal and external fragmentation.

>Internal Fragmentation refers to the fragmentation that happens with in a page.
>External fragmentation is divided to 2 types. Logical and extent fragmentation.
>Logical fragmentation refers to out of order pages within a extent.
>Extent fragmentation refers to out of order extents.

What Reorg does? :

Takes the leaf pages of each index, fixes the internal fragmentation by ensuring that each leaf page is filled as per the fillfactor specified initially while creating the index. Reorg also swaps and rearranges the out of order pages to fix logical fragmentation.

picture of an Sample Extent before Reorg:

picture of Sample Extent after Reorg:

What Rebuild does? :
Rebuild index recreates the index from scratch which means both the leaf and non leaf pages's internal fragmentation is removed.As it rebuilds the index completely it fixes logical fragmentation. Rebuild fixes extent fragmentation most of the
times, but there are a few exceptions.

Let us see the important differences/similarities.

New Page allocation difference - Reorg and Rebuild:
Reorg while fixing internal and logical fragmentation, uses only the existing pages that are allocated to the table.It never allocates new pages and moves around the existing pages to get the internal and logical fragmentation correct.Perhaps thats the reason its called 'Reorg'.

On the other hand Rebuild can allocate new pages, to fix logical and extent fragmentation. Allocating new pages means that Rebuild can sometimes increase the space usage in the data file.

Leaf level pages
As mentioned earlier, Reorg defragments only leaf level pages of a index and Rebuild defrags both leaf and non leaf levels of an index.

Extent fragmentation
Extents are a group of 8 pages, when not present physically in their logical order, is said to be extent fragmented.For ex: page 1-8 of table 1 are present in extent-1.if Pages-9 to 16 of the table, present in extent-2 is not physically stored after extent-1 then the table is said to extent fragmented.

Picture of Sample Extent Fragmented table:

Picture of Extents storage order after Rebuild

Locking - online/offline differences
Reorg is a online process. ie The table remains online during Reorg and updates and Inserts can be done to the table. Reorg takes Indent Exclusive lock at the table level. At page level, it takes exclusive lock only at the page at which is currently working on (swapping/ fixing internal frag ) and leaves the rest of the pages of the table. Once it finishes work on a single page, then it releases the exclusive lock and pulls a exclusive lock the next page it has to work on. If the system is too busy, Reorg sometimes skips the pages.

Rebuild is a offline process and consumes a exclusive lock on the entire table and stops any access to it until the operation finishes.However, Rebuild can be done online using the option ONLINE = ON,if and only if you have a Enterprise edition.

Stats differences
Rebuild of clustered index updates all the statistics assocaited with the table ( both auto and user created ) whereas Reorg doesn't do the same. The STATS_DATE function can be used to check the date at which statistics were last updated.

Log file growths
Both cause the log files to grow. Rebuild can be a minimally logged operation where as REOrg is a fully logged operation.

Stoping / Pausing REOrg
REOrg can be stopped during execution and can continue its execution from it where left.But Rebuild acts like a single transaction and always starts from scratch.

Lob differences
Rebuild of clustered index on a table with LOB column ( varchar(max),varbinary(max),image,xml,text ) fails when performed ONLINE.REOrg of Clustered index compacts LOB objects on the table. REOrg of a non clustered index compacts LOB columns that are part of the included non key columns of the NC Index.

Replacements
Reorg replaces DBCC Indexfrag and Rebuild replaces DBCC DBReindex. The DBCC commands still work on SQL Server 2005, but its recommended to switch to REorg/Rebuild.

Just a note about fragmentation. Fragmentation can hurt your servers performance only when your queries scan the tables instead of Seeking.If your indexes are well selected and accurate, then the queries should use seek operator to fetch the rows and as Seek directly jumps to the row it wants, it doesnt matter how much the table is fragemented or disordered.So, a occasional Reorg or rebuilt should be enough to keep your databases and tables healthy. In general, one perfoms Re0rg when fragmention is around 10 -30 % and rebuild when its greater than 30 %.

Perfmon - useful links

2010-06-21T00:37:00.000-07:00

I have couple of useful links on Perfmon.

1) How to correlate Perfmon data with Profiler Data?

Perfmon data gives us the high level picture of what is causing a performance slowdown.Profiler shows what queries exactly running. Correlating both, tells which processes/queries were exactly responsible for a performance slowdown. The article describes neat and clean way provided by Microsoft tools to correlate perfmon and profiler data. Refer here.
for more details. Note that one needs SQL Server 2005 tools to use correlate functionality.

2) How to capture perfmon data directly into table?

Perfmon counter data can be automatically saved to SQL Servers table instead of binary file (blg) or csv file.Saving Perfmon counter into sql server table allows one to check perfmon counter values like CPU, memory, Disk usage % remotely by just using SSMS/ Query analyzer to connect to the server, without actually logging into the server. The above mentioned technique is one method which works in SQL 2000 to check the CPU of monitored instance remotely. Note that the method explained in last post doesn't work for SQL 2k. Refer here for detailed explanation.

The Perfmon data gets captured into two tables namely CounterDetails,CounterData. The details can be viewed using the following query.

SELECT counterdetails.objectname,
       CASE
         WHEN Isnull(instancename, '!@#$') = '!@#$' THEN ''
         ELSE instancename
       END                        AS instance_name,
       counterdetails.countername AS counter_name,
       counterdata.counterdatetime,
       counterdata.countervalue
FROM   counterdata,
       counterdetails
WHERE  counterdetails.counterid = counterdata.counterid
ORDER  BY counterdata.counterdatetime

Finding CPU utilization in SQL Server 2005:

2010-06-13T23:42:00.000-07:00

Finding the CPU percentage used by SQL Server is perhaps the first step in performance monitoring. Often, finding the CPU % as shown by task manager, from SQL Server can be tricky as SQL Server's sysprocesses and exec_requests gives only CPU ticks and not the exact CPU % used. Logging into each and every server manually and checking the CPU can be a pain if you are administering a large number of servers. So here is a solution.

DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info

SELECT record_id,
Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS eventtime,
sqlprocessutilization,
systemidle,
100 - systemidle - sqlprocessutilization AS
otherprocessutilization
FROM (SELECT record.value('(./Record/@id)[1]', 'int')
AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
'int')
AS systemidle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sqlprocessutilization,
timestamp
FROM (SELECT timestamp,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%') AS x) AS y
ORDER BY record_id DESC

The script provided above provides the approximate CPU usage % as shown in task manager. The result is shown below

Column description is provided below:
SQLProcessutilization: Percentage used by SQL Server process ( sqlserver.exe)
OtherProcessutilization: Percentage used by other processes in the server
Eventtime: Time at which the CPU percentage was recorded.
systemidle: Perecentage of CPU left.
record_id: Just a incremental row id

The CPU percentage is obtained from sys.dm_os_ring_buffers DMV. sys.dm_os_ring_buffers records the CPU percentage
for every one minute and it maintains CPU % for the last 256 minutes. Like all DMVs if the server is restarted then
the DMVs lose the data.

Now for the credits :)

The Script provided above is obtained from the scripts deployed by Performance dashboard tool explained in the last post Performance Dashboard uses the sp [MS_PerfDashboard].[usp_Main_GetCPUHistory] for getting the reports on CPU. I just tweaked the sp to obtain the CPU % for 256 minutes instead of the last 15 minutes returned by Performance Dashboard. Thanks Performance Dashboard again :)

Performance Dashboard - SQL Server 2005 Performance monitoring tool

2010-06-09T23:32:00.000-07:00

Performance Dashboard is a simple performance monitoring tool for SQL Server 2005 released by Microsoft which is absolutely FREE :) . Good and detailed explanation is given by Brad McGhee as usual over here. Let me briefly describe the salient features of it.

How to Set up?

* Download Performance dashboard.msi from here

* Install on the machine from which you want to monitor your production servers. The installation completes in seconds in 3-4 clicks and doesn't demand a restart.

* Proceed to C:\Program files\Microsoft SQL Server\90\Tools\PerformanceDashboard folder ( or the folder where you have installed SQL Server. Performance Dashboard is installed on the folder where where you have already installed SQL Server)

* Copy the Setup.sql script and execute it in the production servers to be monitored.

Thats it. You are ready to go.

How to use it?

* Open SSMS on the machine you installed Performance Dashboard.
* Connect to the instance/SQL Server to be monitored on the SSMS
* Right Click on the connect server on the object explorer
and Select Reports .

* Pick Custom Reports
* Select performance_dashboard_main.rdl from C:\Program files\Microsoft SQL Server\90\Tools\PerformanceDashboard
* And then experience the magic :)

Opening Dashboard Screen shot is provided below.

Why I like it?

* Can check CPU/Buffer cache hit ratio and overall health of Database Servers remotely from SSMS.
* Quickly check the recently ran expensive queries ( as per by IO/CPU/ along with their Query Plan
* Check the Database Size and Log file size, growth, and other paramaters with in a few clicks.
* Provides Missing indexes and suggestions to improve peformance. Also provides number of seeks/scans on the objects.
* Provides DB level IO stats like Reads, Write and their wait times.
* All these brilliant info at almost 0% cost on the monitored server.

Now for few minor cons.

* An alerting feature along with the tool would have been great.
* Also, there are one or two minor bugs for which solutions are already available.
* Ability to add perfmon counters as per our choice would have been even better.
* One cant do a direct copy or paste from the report, has to export to PDF /excel and then copy the required data.

But, am I asking for bit too much on a free tool. Definitely yes :) Inspite of these little cons,Performance Dashboard is a must have for all DBAs, especially if you are monitoring 25+ servers. Just very handy for quickly checking the overall state and health of the server without actually logging into the server. Other than showing larger picture, it helps even to narrow down up to the query level. Simply awesome stuff.

Scalar functions - on where clause

2010-06-06T19:31:00.000-07:00

Scalar functions as discussed in the last post can be a performance bottleneck.Another place where Scalar functions ( both user defined and system ) can become a performance bottleneck is when used on a where clause of the query. Scalar functions when used on a where clause of a query can make the optimizer pick a table scan, even if the index is present, making the index useless.

/****** Object:  Index [ncix_Supplier_product_customer_trans_date]   ******/
CREATE NONCLUSTERED INDEX [ncix_Supplier_product_customer_trans_date]
  ON [dbo].[Supplier_Product_Customer] ( [trans_date] ASC )
  ON [PRIMARY]

A non clustered index on trans_date column of supplier_product_customer table has been created.When one needs to find the list of transactions for a particular day ( cutting the time part of the , the most common method of writing the query would be

SELECT *
FROM   [Supplier_Product_customer]
WHERE  Datediff(dd, [trans_date], '11/19/2009') = 0

But,the problem with such a query is that it takes table scan instead of using the Non clustered index created on trans_date. The reason is that scalar function datediff used on trans_date column stops the query analyzer from using the index.

A better way of writing it would be

SELECT *
FROM   supplier_product_customer
WHERE  trans_date >= '20091119'
       AND trans_date < '20091120'

The picture above shows a non clustered index seek instead of table scan. There are few other common scenarios where functions can be avoided. Let me briefly list them here

* Left() string fuction can be effectively replaced by like
'str%'
* Usage of upper/lower string functions can be avoided
when the database has a case insensitive collation.
* isnull(col1,'xyz') = 'xyz' can be replaced by col1 = 'xyz'
or col1 is null
Note that OR conditions do use indexes and but at times they don't.
Please check before use.
* Getting data older than 60/n days query.
Standard way of doing it would be

Where datediff(dd, trans_date, getdate()) > 60

Replaced by

Where trans_date < CONVERT(VARCHAR, getdate()-60, 112)

To Conclude, one should try the best to avoid having scalar functions in where clauses.

Performance comparison - Functions versus Joins

2010-05-25T02:36:00.000-07:00

User defined Scalar function is often chosen as a easier alternative for joining two or multiple tables while writing queries by developers. Other reason given by 'Few' developers is that its provides better readability on a long query. Both are bad reasons for using functions as scalar functions when used inappropriately can potentially kill the performance.

Scalar function when used on a query is executed for every row of the result set, just like a cursor would do. Row by Row processing is very very expensive when compared to SET based processing provided by joins. Scalar functions also makes indexes on the joined tables less effective or even useless.Above factors make joins, in general, a better option when they can replace a scalar function.

We will see the same with an example.

Consider the following tables student , student_marks . Student table contains student details and student_marks contain marks obtained by students on 5 subjects. Table definition and random data population scripts are provided below.

/***********************************************/
/************* Table [dbo].[student_marks]******/
CREATE TABLE [dbo].[student_marks]
  (
     [sid]          [INT] NOT NULL,
     [marks]        [INT] NULL,
     [subject_name] [VARCHAR](50) NOT NULL,
     [subject_id]   [INT] NOT NULL,
     CONSTRAINT [PK_student_marks] PRIMARY KEY CLUSTERED ( [sid] ASC,
     [subject_id] ASC )
  )
ON [PRIMARY]
GO

/****** Table [dbo].[Student] ******/
CREATE TABLE [dbo].[Student]
  (
     [sid]          [INT] NOT NULL,
     [student_name] [VARCHAR](50) NULL,
     CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [sid] ASC )
  )
ON [PRIMARY]
GO

Random data population scripts

/*************Student table data population*********/
DECLARE @id INT
SET @id = 0
WHILE @id <= 100000
  BEGIN
      INSERT INTO student
      SELECT @id,
             CHAR(( @id % 26 ) + 65) + '-' + CONVERT(VARCHAR, @id)

      SET @id = @id + 1
  END
GO

/*************Student_marks table population**********************/
INSERT INTO student_marks
SELECT sid,
       Round(Abs(( Rand(sid + x.subid) * 1000000000000 ) - Round((
                       ( Rand(sid + x.subid) *
                         1000000000000 ) / 100 ), 0) * 100), 0),
       x.subject,
       x.subid
FROM   student,
       (SELECT 'math' AS subject,
               1      AS subid
        UNION
        SELECT 'physics',
               2
        UNION
        SELECT 'chemistry',
               3
        UNION
        SELECT 'computer_science',
               4
        UNION
        SELECT 'tamil',
               5) AS x

The Scripts provided would insert 100,001 rows in student table and 500,005 rows student marks table.Primary key clustered index has been created on sid column on student table and sid,subject_id columns on student_marks table.

Assume that the requirement is to provide the student names and marks of all students in the school.Solution using scalar function would involve creating a function to get student name from student table and querying the student_marks table for obtaining rest of the details. Simple function definition would look like

/****** Object:  UserDefinedFunction [dbo].[student_name]    ******/
SET ansi_nulls ON
GO

SET quoted_identifier ON
GO

CREATE FUNCTION [dbo].[Student_name](@sid INT)
RETURNS VARCHAR(50)
AS
  BEGIN
      DECLARE @student_name VARCHAR(50)

      SELECT @student_name = student_name
      FROM   student
      WHERE  sid = @sid

      RETURN @student_name
  END

After creating the function, the following query using scalar function is executed.

SET statistics io ON
GO

DECLARE @dt DATETIME
SET @dt = Getdate()
SELECT dbo.Student_name(sid),
       *
FROM   student_marks
WHERE  sid < 20000

SELECT Datediff(ms, @dt, Getdate())

Perfomance Stats are provided below:
Time taken :55640 ms ( 0:56 Seconds)
Rows Returned :100000
IO details : Table Table 'student_marks'. Scan count 1, logical reads 415, physical reads 56, read-ahead reads 412, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query plan:

The query plan doesnt show whats exactly happening with in the function/
how many times was the function precisely called.So, to find the same, let me use the DMV dm_exec_query_Stats

SELECT Substring(TEXT, ( statement_start_offset / 2 ) + 1, ( ( CASE
              statement_end_offset
              WHEN -1 THEN Datalength(TEXT)
              ELSE statement_end_offset
                                                               END
       - statement_start_offset ) / 2 ) + 1) AS statement_text,
       Isnull(Object_name(objectid), '-')    AS obj_name,
       execution_count,
       max_elapsed_time,
       max_logical_reads,
       max_logical_writes
FROM   sys.dm_exec_query_stats
       OUTER APPLY sys.Dm_exec_sql_text(plan_handle) AS sqltext
WHERE  TEXT NOT LIKE '%dm_exec_query_Stats%'
ORDER  BY execution_count DESC

Execution count column on dm_exec_query_stats DMV clearly shows that the query Select dbo.student_name(sid),* From student_marks where sid < 20000 in the function dbo.student_name has been executed 10000 times , which is exactly the same number of rows returned. So its basically passing a number ( sid ) and making the functions' code execute over and over which makes it painfully slow.

Let us Consider the join based solution:

SET statistics io ON

GO
DECLARE @dt DATETIME
SET @dt = Getdate()
SELECT student_name,
       student_marks.*
FROM   student_marks,
       student
WHERE  student_marks.sid = student.sid
       AND student_marks.sid < 20000
SELECT Datediff(ms, @dt, Getdate())

Performance Stats :
Time taken : 2876 ms ( 2.9 Seconds)
Rows Returned: 100,000
IO details : Table 'student_marks'. Scan count 1, logical reads 415, physical reads 3, read-ahead reads 412, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Student'. Scan count 1, logical reads 60, physical reads 1, read-ahead reads 58, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query plan:

Join based solution completes the query in less than three seconds, which is 20 times faster than Scalar functions. IO also indicates lesser number of reads. The student table is also read only once against 100,000 times as in the case of scalar functions. Query Stats DMV confirm the same.

So the conclusion is, one should always avoid using scalar functions as much as possible and should be used only when we don't have any other option to replace it.Complex string manipulation requirements or date calculation requirements are perhaps, plausible excuses for Scalar functions, but still they have to be used with caution as they have been found as performance bottlenecks more often than not.

SQLDiag

2010-05-19T00:28:00.001-07:00

When one needs to monitor a servers performance, one has to make use of the Performance monitor to capture the system parameters and obtain the bigger picture, identify the bottleneck and then use profiler utility to identify the queries causing the performance slowdown.

Instead of using multiple tools to gather performance data, SQLDIAG is a single utility which is capable of capturing the following.

* Queries executed and corresponding events.
* Performance counters
* Error logs
* SQL Server blocks and resource usage information.
* SQL Server version and configuration information.

SQLDiag is a command line utility where one can configure the things to be captured in a XML file.SQLDiag is found in the drive in which SQL Executables are installed,with the path '..\Microsoft SQL Server\90\Tools\Binn\' . SQLDiag takes a few input parameters like XML file to be taken as input, place to write the captured results etc. Once started provides the captured results into the specified folder.
Details about useful output files are provided below.

1) SQLDIAG.BLG - Stores the perfmon counters values asked for
2) _sp_trace.trc - Provides the profiler details/query
details
3) ***_sp_SQLDiag_shutdown.OUT- Provides the sqlerrorlog,current
running queries,open transaction,configuration info, blocking query
info taken at the time of stopping SQLDiag.

Sample call is provided below:

START SQLDIAG /I "%cd%\customized.XML" /O "%cd%\SQLDiagOutput" /P

"%cd%" Refers to the current directory. customized.XML is the XML I prepared for capturing data. SQLServer by default ships SQLDIAG.XML which can be used to capture data. SQLDIAG.XML captures a whole lot of data with too many counters and too
many profiler events. Instead, I have created a customized XML which grabs important 15 - 20 counters and queries that are executed against the database. Customized.xml can be downloaded from here. There are very few resources on SQLDiag the
net.Refer here for more details.

SQLDiag's advantages and drawbacks are provided below:

Advantages:

* Single tool to capture all the data required.
* Can be easily reused across servers as its just a single XML file which
contains all the configuration and settings
* Can be run as a Service

Disadvantages :
* Cant be configured from a remote machine. One needs to be logged in to the
actual server.
Stores the data only on the monitored instance.
* Changing XML can be bit tedious and difficult.
* No performance gain when compared to profiler/perfmon. Incurs the same
amount of resouces as other tools.
* Doesnt store data on the database which can make it easier for analysis.
Stores only on trc/text files.

Getting the IP Address and port no on SQL Server 2000,SQL Server 2005

2010-05-11T04:07:00.000-07:00

This is my 50th Post. Thanks a lot for all the readers for your support.

Now for the actual topic.
Finding the IP address and port number of a SQL Server from a remote client can be tricky especially if you are on SQL Server 2000.I am never a big fan of xp_cmdshell . So I am not using xp_cmdshell 'ipconfig'. The alternative method that works for me is reading the SQL Error log thro sp_readerrorlog . Run the following command

EXEC Sp_readerrorlog
GO

sp_readerrorlog provides an output as shown below.

If you scroll 20 to 25 lines ( varies from server to server ) of output, you should find the text 'SQL Server is listening on IP Address : Port no'. Note that we can also find the port at which SQL Server is listening for connections. By default SQL Server listens for connections at 1433.

If you are on SQL Server 2005, then you are life is made lot easier using the DMV sys.dm_exec_connections.Execute the following query

SELECT *
FROM sys.dm_exec_connections

The local_net_address,local_tcp_port columns on the DMV dm_exec_connections provides the IP Address of the SQL Server and the port at which SQL Server listens for connections. Additionally columns Client_net_address,Client_tcp_port indicate the ip addresses that are currently connected to SQL Server and the the source ports from which they are connecting to.

Clustering - few useful documents

2010-05-03T20:16:00.000-07:00

This is One of best articles I have read on clustering.
Very complete, and provides info on just about every task we happen to perform
on clustered servers. Must have for a DBA, if any of your servers are clustered.
Download from here. Best part its a white paper provided by Microsoft itself :)

In case, if you want to read on how to install clustered sql server with some simple
screen-shots, you can also refer to embarrassingly simple article written by Brad Mcghee here.

Enjoy.

Plan Cache - Part 3 - What gets recompiled

2010-04-30T21:56:00.000-07:00

As a continuation of series of posts on Plan caching, let us get started with Part -3
Earlier posts are given below:

Plan caching - part-1 - Introduction
Plan caching - part-2 - What gets cached

Last post explained how stored procedures,prepared statements are cached. But there are a few scenarios where caching doesn't work, and object/query plan in the cache gets recompiled. Recompilation is the process where SQL Server finds that the plan in the cache is no good/invalid and creates a new plan for the query submitted. So lets get started. The following scenarios cause recompilation.

* A change in the structure/definition of the object
If the structure of the stored proc/table(Add/delete column, constraint,
index) has changed since last execution then the plan is obviously
invalid and hence it has to be recompiled.

We are using the same stored procedure test_plan_caching defined in the last post. Consider the following script.

DBCC freeproccache --Clear the cache as usual
EXEC Test_plan_caching 'moscow' --Call the SP

GO

ALTER PROCEDURE [dbo].[Test_plan_caching] @city VARCHAR(50)
AS
  SELECT supplier_xid,
         product_xid,
         trans_city,
         qty,
         comments,
         balance_left
  FROM   supplier_product_customer
  WHERE  trans_city = @city
  ORDER  BY supplier_xid DESC
GO

--Forcing an alter to the sp, though no logical change has been done
EXEC Test_plan_caching 'moscow'--Call the SP

SELECT TEXT,
       usecounts,
       size_in_bytes,
       cacheobjtype,
       objtype
FROM   sys.dm_exec_cached_plans
       OUTER APPLY sys.Dm_exec_sql_text(plan_handle) AS sqltext
WHERE  TEXT NOT LIKE '%dm_exec_cached_plans%'

Alter made on the sp forced the optimizer to recreate the plan.
Similar changes that can cause such recompilations are adding/dropping an index,changing a trigger/proc/view definition etc.

* A change in the connection parameters using SET command .

Changing the following connection parameters using SET keyword can cause recompilation.

ANSI_NULL_DFLT_OFF/ANSI_NULL_DFLT_ON/ANSI_NULLS/ANSI_PADDING/ANSI_WARNINGS
/ARITHABORT/CONCAT_NULL_YIELDS_NULL/DATEFIRST/DATEFORMAT/FORCEPLAN/LANGUAGE
/NO_BROWSETABLE/NUMERIC_ROUNDABORT/QUOTED_IDENTIFIER

DBCC freeproccache
GO

EXEC Test_plan_caching 'San Franciso'
GO

SET ansi_null_dflt_off ON
GO

EXEC Test_plan_caching 'San Franciso'
GO

Query stats DMV clearly shows two plans. One for the plan generated before setting ANSI_NULL_DFLT_OFF and other for the plan with different value on ANSI_NULL_DFLT_OFF.

Note that a change of option 'ANSI_NULL/QUOTED_IDENTIFIER' doesn't affect a Stored proc or Table valued function. But it does affect Adhoc queries or prepared statements.

* A Forced recompilation

When a Object is marked for recompilation using sp_recompile stored procedure or when with recompile hint is used then query is recompiled.
Example:

EXEC Sp_recompile 'test_plan_caching'

The statement above ensures that next call made to test_plan_caching results in a recomplie.

When a procedure is created with an option WITH RECOMPILE, any call made to the stored proc
is always recompiled.

usage of hint OPTION RECOMPILE causes a statement level recompilation.
The scenarios where these options are useful are potential posts on their own. Will cover them soon.

* A change in statistics, caused by changes done to most of the rows in the table.
A rough algorithm of how SQL Server decides whether the table needs an updated statistics is given below. The algorithm changes is based on RT factor ( Recompilation threshold ) which depends on the number of changes made to the table.
If a table contains less than 500 rows then atleast 500 changes are required to cause a recompilation.If a table has more than 500 rows, then number of changes required are 500 + 20 % of rowcount.

As the algorithm changes with version and service pack releases, its approximate.

Plan cache - part 2 - What gets cached

2010-04-24T22:07:00.001-07:00

In general, we can classify queries submitted into three catogories.

They are
1) Adhoc queries -> The ones submitted thro query analyzer or the queries from applications which are not prepared statments ( executed as just statement without setting any parameter ).
2) SQL objects -> calls made to Stored procs, Triggers, UDFs, system and extended procs
3) Prepared Statements -> Sp_executesql with parameters,Prepared statements from applications.

Calls to sql objects and prepared statements are more likely to be cached. Let us see it in detail.Consider the following adhoc query:

DBCC freeproccache -- Just to free the cache before we start the experiments.

SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city = 'San Franciso'
ORDER  BY supplier_xid DESC

Let us change the parameter and execute again

SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city = 'Perth'
ORDER  BY supplier_xid DESC

To check whether the query was cached or not let me fire the query on DMV cached plans.

SELECT TEXT,
       usecounts,
       size_in_bytes,
       cacheobjtype,
       objtype
FROM   sys.dm_exec_cached_plans
       OUTER APPLY sys.Dm_exec_sql_text(plan_handle) AS sqltext
WHERE  TEXT NOT LIKE '%dm_exec_cached_plans%'

The usecounts column has a value 1 which indicates clearly that the query was not cached. By default, adhoc queries are cached only when there is a exact match. A hange in parameter, even a change in case or additional space in the query results in sql server not reusing the query plan prepared eariler.

Let us make it a Stored proc and try the query again.

CREATE PROCEDURE Test_plan_caching @city VARCHAR(50)
AS
  SELECT supplier_xid,
         product_xid,
         trans_city,
         qty,
         comments,
         balance_left
  FROM   supplier_product_customer
  WHERE  trans_city = @city
  ORDER  BY supplier_xid DESC

GO

EXEC Test_plan_caching 'San Franciso'

GO

EXEC Test_plan_caching 'Perth'

GO

A query to DMV cached plan shows that the query plan has been reused.

Now let us try sp_executesql method. Consider the following script

DECLARE @SQLString NVARCHAR(1000)
DECLARE @paramstring NVARCHAR(100)
DECLARE @city VARCHAR(100)

SET @SQLString = 'Select supplier_xid,product_xid,trans_city,qty,comments,balance_left from ' + 'Supplier_Product_Customer where trans_city = @variable order by supplier_xid desc '
SET @paramstring = '@variable varchar(100)'
SET @city = 'San Franciso'

EXEC Sp_executesql
  @SQLString,
  @paramstring,
  @variable = @City

SET @city = 'Perth'

EXEC Sp_executesql
  @SQLString,
  @paramstring,
  @variable = @City

Note that we have changed the parameters by setting a new value to input variable and executed the same query.

DMV cached plan shows that query is getting reused. Bear in mind that mere usage of sp_executesql without using input parameter doesnt reuse the plan.

Note that the object type column on DMV cached plan indicates the kind of object that has been cached. Proc refers to procedure, Prepared refers to prepared plans created by sp_executesql and by application queries and Adhoc refers to adhoc queries.

There are still a few exception scenarios where a stored procedure plan doesnt get effectively reused.We will explore them more in the next post.

Plan cache - Introduction

2010-04-16T22:30:00.000-07:00

When a query is submitted to SQL server, the query optimiser checks the syntax, verifies the objects and prepares a query plan. A query plan prepared is stored in a area of memory called plan cache . A plan placed in plan cache will be reused when a same query or 'similar query' is submitted to the query analyzer. Preparing a plan everytime can be expensive and time consuming task
and hence the query optimiser attempts to resuse a plan when it is supposed to.

A quick example.

DECLARE @dt DATETIME

SET @dt = Getdate()

SELECT supplier_xid,
       product_xid,
       trans_city,
       qty,
       comments,
       balance_left
FROM   supplier_product_customer
WHERE  trans_city IN( 'San Franciso', 'Perth' )
ORDER  BY supplier_xid DESC

SELECT Datediff(ms, @dt, Getdate())

First, the above query is executed. The table contains about 110,000 rows and the query returns about 45000 rows.The query returned the results first time in 1217 ms.

Let us execute the same script again. The script completes in 626 ms which is about half of first execution's time. When executing for the second time,The table's query plan is already in the cache and hence it is reused. We can check whether our query is cached or not using the dmv sys.dm_exec_cached_plans. The following query helps us find the same.

SELECT TEXT,
       usecounts,
       size_in_bytes,
       cacheobjtype,
       objtype
FROM   sys.dm_exec_cached_plans
       OUTER APPLY sys.Dm_exec_sql_text(plan_handle) AS sqltext
WHERE  TEXT NOT LIKE '%dm_exec_cached_plans%'

Observe the result provided above. The text column provides the query and usercounts indicates how many times the plan has been used. For the above script, usecounts value is 2 and hence it indicates that the query has been reused.

In general, the queries that are 'likely' to be cached and used are as follows.

* Stored procedures, Triggers, Table valued functions.
* Prepared Statements used from applications ( from java/.net/c# etc )
* Queries executed using sp_executesql with input paramters.

As always there are a few exceptions and thats the reason the word 'likely' is in place.

What is written here is just the tip of the iceberg and we will explore more and more on the caching, pros and cons of caching,and the way plan cache works in the next few posts.

Job still runs inspite of disabling

2010-04-12T01:27:00.000-07:00

When one has to disable a job/group of jobs, common way is to do it from
Enterprise manager or using the Graphical user interface. At times, when one is
trying to update for a group of jobs then one way people attempt to do it
is by updating sysjobs system table directly. ie.,

UPDATE sysjobs
SET    enabled = 0
WHERE  name IN ( 'job a', 'job b', 'job c' )

This is a very dangerous way of doing it, as it doesn't disable completely.
Worse is that when you update sysjobs directly,the job appears to be disabled
to on the GUI, but still the job continues to run as scheduled.

For example consider the following job Test.

The job is currently enabled.

UPDATE sysjobs
SET    enabled = 0
WHERE  name = 'test'

After executing the query provided above, the job appears to be disabled as shown below.

However, the job continues to run as scheduled. The reason is that the properties
and other attributes of the job are kept in memory and a update to sysjobs
doesnt update the SQL Agent. The correct way of enabling/disabling a job
or updating any property of a job will be to use system stored procedure sp_update_job

Sample call for disabling a job is shown below.

EXEC msdb.dbo.Sp_update_job
  @job_name = 'test',
  @enabled = 0

For updating a job schedule use sp_update_schedule/sp_update_jobschedule
For more details refer books online here

Also, just a short script which provides a script to disable all
jobs in your server.

SELECT 'EXEC sp_update_job @job_name = ''' + name + ''',@enabled = 0 ' + CHAR(10) + ' GO ' + CHAR(10)
FROM   sysjobs

View the results of the script in text mode ( instead of grid)
to get the script correctly.

Sparse column - Maximum size of a row

2010-04-07T23:53:00.000-07:00

Just to clarify, by including a sparse column, the maximum size of the row DOES NOT reduce to 8018 bytes.Quite a few sites have mentioned that sparse column reduces the max row size to 8018 , but its not true.What is true is that the total size occupied by sparse columns alone shouldn't exceed 8019 bytes. The row size ( sparse columns size + normal columns size ) can be greater than 8018 bytes and has the normal row limitation of 8060 bytes. The script provided below illustrates the same.

DROP TABLE [sparse_col]
GO
CREATE TABLE [dbo].[sparse_col]
  (
     [dt]        [DATETIME] NOT NULL,
     [value]     [INT] NULL,
     data        CHAR(500) NULL,
     sparse_data CHAR(7500) SPARSE NULL,
  )
GO
INSERT INTO [sparse_col]
SELECT Getdate(),
       0,
       'sparse_data',
       'sparse data'
GO

The insert is successful without any errors.

Note that the sparse column 'sparse_data' has a size of 7500 bytes.
Total size of the row when all columns have non null value will exceed 8018 bytes.
Actual size of the row can be checked using DMV dm_db_index_physical_stats as shown below.

DECLARE @dbid INT;
SELECT @dbid = Db_id();

SELECT Object_name(object_id) AS [table_name],
       record_count,
       min_record_size_in_bytes,
       max_record_size_in_bytes,
       avg_record_size_in_bytes
FROM   sys.Dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, 'Detailed')
WHERE  Object_name(object_id) = 'sparse_col'

The size of the row is 8045 bytes, which is above 8018 bytes.Thus its clear that adding a column as sparse column doesnt reduce the size of the row to be 8018 bytes.

Now let us see an example which generates the actual error.
Run the following script.
The script used earlier is slightly modified by changing the 'data' column to sparse. Rest of the structure remains the same with no changes done to the length of the columns.So, the sparse columns on the table are data,sparse whose combined size are 8000 + few bytes used for internal use for storing sparse data.

DROP TABLE [sparse_col]
GO
CREATE TABLE [dbo].[sparse_col]
  (
     [dt]        [DATETIME] NOT NULL,
     [value]     [INT] NULL,
     data        CHAR(500) SPARSE NULL,
     sparse_data CHAR(7500) SPARSE NULL,
  )
GO

INSERT INTO [sparse_col]
SELECT Getdate(),
       0,
       'some data',
       'sparse data'

The insert fails with the error
Msg 576, Level 16, State 5, Line 1
Cannot create a row that has sparse data of size 8031 which is greater than the allowable maximum sparse data size of 8019.

The reason is that the total size consumed by sparse_data,data column is 8000 bytes + 31 bytes used for internal use which exceeds the total size allowed (8019 bytes) for sparse columns.

Reference: SQL Server 2008 Internals by Kalen Delaney :)

Sparse Columns

2010-04-02T06:48:00.000-07:00

Sparse columns are a new column property introduced in SQL Server 2008, to improve the storage of NULL values on columns.When a column is defined as SPARSE, and if a null is inserted on the column then the column doesnt occupy any space at all.

Some facts:

* A Non varying column with a NULL value occupies the entire length of the column.( Datetime column with null occupies 8 bytes)
* A varying column(varchar) with a NULL value occupies a minimum of two bytes.

So, by defining a column sparse one can save the space that is wasted when NULL values are inserted.But Sparse columns come at a cost. When a sparse column
contains NON NULL value / Valid value, it consumes extra 4 bytes of space. So,
one is expected to use sparse columns only when 90% of the value on the sparse column is expected to be null.

Example:

Consider the following table.

CREATE TABLE [dbo].[sparse_col]
  (
     [dt]    [DATETIME] NOT NULL,
     [value] [INT] NULL,
     data    CHAR(500) NULL
  )

Data column doesnt have a sparse property defined on it. Let me insert 10,000 rows using the following script

DECLARE @id INT

SET @id = 1

WHILE @id <= 10000
  BEGIN
      INSERT INTO [sparse_col]
      SELECT Getdate(),
             @id,
             NULL

      SET @id = @id + 1
  END

The spaceused occupied by the table is 6280 kb as shown below.

Let us recreate the table with 'data' column defined as a sparse column.

CREATE TABLE [dbo].[sparse_col]
  (
     [dt]    [DATETIME] NOT NULL,
     [value] [INT] NULL,
     data    CHAR(500) Sparse NULL
  )

The same script provided above is used to insert the rows. The space occupied is just 264KB after switching to sparse column.

Size of a row :

Size of each row in SQL Server 2008 is limited to 8060 bytes. Sparse columns doesnt allow one to exceed this limitation.The size of all Non null sparse columns of a row is limited to 8019 byes.

Example:

Let us change the table definition , by adding addtional column

ALTER TABLE sparse_col ADD data2 CHAR(8000) Sparse NULL

INSERT INTO sparse_col
SELECT Getdate(),
       5,
       NULL,
       NULL

The above insert works. But the below doesnt.

INSERT INTO sparse_col
SELECT Getdate(),
       5,
       'x',
       'x'

The insert fails with a error indicating that the size of the row exceeded 8060 byte limitation.

Adding more than 1024 columns:
By default SQL Server 2008 allows 1024 columns per table. But, by using sparse columns one can increase the number of columns to 30,000. However, there can be a maximum of 1024 non sparse columns on the table and the rest have to be sparse columns. The sparse columns defined will have to be grouped using the Column set feature introduced in SQL Server 2008. Column set is a untyped XML column which will group all the sparse columns on the table. The Column set column is a virtual column which doesnt get stored in the table. For more details refer here.

A table with more than 1024 columns is called a wide table and it can be defined with the following script.

DROP TABLE [sparse_col]

GO

CREATE TABLE [dbo].[sparse_col]
  (
     [dt]                  [DATETIME] NOT NULL,
     [value]               [INT] NULL,
     data                  CHAR(500) Sparse NULL,
     specialpurposecolumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  )

A table with the column set feature is created using the above script. SpecialPurposeColumns is a column set which will be grouping all the sparse columns in the table.The script provided below adds more sparse columns to the table.

DECLARE @id INT

SET @id = 1

DECLARE @sql NVARCHAR(100)

WHILE @id < 25000
  BEGIN
      SET @sql = 'ALTER TABLE [sparse_col] ADD Col' + CONVERT(VARCHAR, @id) + '  int sparse null '

      EXEC Sp_executesql @sql

      SET @id = @id + 1
  END

The above script adds 25000 columns to a table. One doubts whether its neccassary.

Sparse columns do come with many restrictions like sparse columns cant participate in primary keys,sparse columns cant have default values etc. For complete set of restrictions refer here

Changing Server name - sp_dropserver,sp_addserver

2010-03-26T22:40:00.000-07:00

When one changes the name of the computer running the SQL Server, one needs to
perform the following steps to ensure that SQL Server also uses the new updated
name.
First to check whether the SQL Server has the correct name,
execute the following query

SELECT @@SERVERNAME                 AS oldname,
       Serverproperty('Servername') AS actual_name

@@Servername gives the computer name that has been set/registered with SQL Server
Serverproperty('Servername') provides the newly updated name from windows.

If @@servername,Serverproperty('Servername') results are different then
one should update the new name on the SQL Server. To update, follow the steps
provided below.

step 1 :- sp_dropserver/sp_addserver

EXEC Sp_dropserver
  'oldservername'

>
EXEC Sp_addserver
  'newservername' ,
  'local'

step 2 :- update sysjobs
Applies only for SQL 2k.

USE msdb
UPDATE sysjobs
SET    originating_server = Serverproperty('Servername')

step 3 :- Restart SQL Service
Restart SQL Services for the changes to take effect.

Even if one doesnt update the Servername on SQL Server, SQL Server will still function,but one can face a few issues like the error

'Error 14274: Cannot delete a job that originated from an MSX server'
The above error appears, when one attempts to change the SQL jobs properties like enabled/disabled and if the Servername is not correctly updated.
Linked server configurations may not work correctly. So in general, if the servername is changed its better to update it on SQL Server using the above mentioned steps.
For more details refer here

Finding Space Used,Space left on Data and Log files

2010-03-22T08:10:00.000-07:00

The data files and log files in SQL Server grow as specified by the growth property of the files.The growth property is specified either in % of existing size or in terms of MB. After a file grows, the newly allocated space is used for the transactions that happen in the database. One of the responsiblities of the DBA can be to keep track of how much of space is used and how much is free
with in the data and the log files. Let us run thro the options we have in SQL Server 2000 and SQL Server 2005.

Option 1 : Perfmon counter

A popular option can be to use perfmon counters, but the drawback is perfmon counters provide only the data file size, and not Data file used size, Data file space left. However, they do provide the space left and space used details for log files.

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Databases%'
AND counter_name LIKE '%File%'

For SQL 2000 use sysperfinfo

Option 2 : DBCC Showfilestats()

DBCC Showfilestats takes the file id as parameter and provides used space and free space in terms of extents.

Example:

DBCC showfilestats ( 1 )

But, DBCC Showfilestats doesnt provide the info for log files and does it only for data files. So, to help our cause it should be used in combination with perfmon counters.Another word of caution about DBCC Showfilestats(). It is 'undocumneted'.
So, I still prefer a alternative way for checking these details.

Option 3: Fileproperty/sysfiles
The third option which perhaps meets all our requirements is the one using sysfiles system table and fileproperty function. Sysfiles provides the size of data and log files in the database. Fileproperty takes filename( from sysfiles table) and the string 'spaceused' as parameter and provides the actually used by the file.

Example:

SELECT filename,
       name,
       size,
       Fileproperty(name,'SpaceUsed') AS spaceused
FROM   sysfiles

The script provided below can be used against any database SQL Server 2000/2005/2008 and extracts the space used and spaceleft information for all the databases in the server.

DECLARE  @rowcnt INT
DECLARE  @iterator INT
DECLARE  @dbname VARCHAR(200)
DECLARE  @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info (
  [Database_Name]    SYSNAME    NOT NULL,
  [File_ID]          SMALLINT    NOT NULL,
  [File_Type]        VARCHAR(10)    NOT NULL,
  [File_Name]        SYSNAME    NOT NULL,
  [File_Path]        VARCHAR(500)    NOT NULL,
  [File_Size_in_MB]  INT    NOT NULL,
  [Space_Used_in_MB] INT    NOT NULL,
  [Space_Left_in_MB] INT    NOT NULL)
CREATE TABLE #db (
  dbid INT,
  name VARCHAR(200))
INSERT INTO #db
SELECT dbid,
       name
FROM   MASTER.dbo.sysdatabases
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
  BEGIN
    SELECT @dbname = name
    FROM   #db
    WHERE  dbid = @iterator

    SET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,

[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles;
'

    EXEC( @exec_sql)

    SET @iterator = @iterator + 1
  END
SELECT *
FROM   #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info

Resultset is provided below: