SQL Server 2008 Virtual Lab

TechNet Virtual Lab: Administering Servers by Using Declarative Management Framework (DMF) Policies
TechNet Virtual Lab: Authoring Reports Using SQL Server 2008 Reporting Services
TechNet Virtual Lab: Implementing Change Data Capture (CDC) in SQL Server 2008
TechNet Virtual Lab: Learn How to Build Occasionally Connected Applications with SQL Server 2008
TechNet Virtual Lab: What's new in SQL Server 2008 for Database Administrators
TechNet Virtual Lab: Working with the new DATE data type in SQL Server 2008

How to Install SQL Server Client Tools from Command Line

Command line installation of Client Tools
driveletter:\Servers\Setup>start /Wait setup.exe ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_BooksOnline

FAQ: SQL Server Connectivity Troubleshooting

Connectivity issues are very common in SQL Server 2005 since there are few settings to be done at server level and OS level to enable the server for remote connection. There are many documents available from Microsoft Protocol team gives step by step troubleshooting process. I always refer those Bolgs whenever I see such issues in forums. Then I thought to have a consolidated list where all the links are available

(a) Check whether the server is configured to accept remote connections. Use SAC to enable server for remote connection
.
(b) Ensure that the SQL Browser service is started (SQL Browser equivalent in 2000 is SQL Server Resolution Protocol (SSRP) )

(c) Determine whether clients are specifying the correct port (for using fixed ports with named instances) in the server alias or connection string.

(d) Check whether the client’s network protocols are enabled and configured to correctly handshake with those of the server.

(e) Be sure you have permission to connect on the server’s endpoints.

(f) When using encryption, be sure the server and client certificates match (that is, check their Common Name (CN) and any other relevant attributes) and are installed and configured correctly on both sides.

(g) Make certain that your firewalls are configured to permit the required network traffic.

(h) Check to see whether your users have permission to log in to the server and access the specified database.

(i) Make sure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn’t been altered or removed from the system.

Refer following KBs and Blogs for more info :-
http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

How to Detect Deadlocks from SQL Profiler

Deadloack detection is a very common requirement in Development (even in Production) environment. There are couple of method to detect Dead Locks. In SQL Server 2000, trace flag 1204 can be set to capture the processes involved in the deadlock. The output is text based but provides valuable information about the types of locks and the statements that were executing at the time of the deadlock. In addition to this approach, SQL Server 2005 offers the ability to capture detailed deadlock information via the SQL Server Profiler. The process of Dead Lock detection in SQL Server 2005 is as follows

(a) Create a new trace, Select a Blank template; this leaves the selection of all the events, data columns, and filters to you.

(b) Add the Locks:Deadlock graph event to the trace from the Locks category. An additional tab appears on the Trace Properties window, named Event Extraction Settings.

(c) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file. You could also export the results after the trace has been run by using the File, Export option.

SQL Server 2005 Pricing and Licensing

Licensing is one area where I always struggle to give my suggestion because it’s a legal issue and the suggestion should politically correct. Check this document right from Microsoft...

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc


Also Check this thread


Developer and Express edition Licensing Model always need little clarificaiton

Developer Edition Licensing

The Developer Edition of SQL Server 2005 is available for a fixed price of $49.95(subjected to change). The Developer Edition is licensed per developer and must be used for designing, developing, and testing purposes only.

Express Edition Licensing

The Express Edition of SQL Server 2005 is available via free download Microsoft site. Developers can redistribute it with their applications at no cost by simply registering for redistribution rights with Microsoft. The Express Edition does not require a CAL when it is used on a standalone basis. If it connects to a SQL Server instance running Enterprise Edition, Standard Edition, or Workgroup Edition, a separate user or device CAL is required unless the SQL Server instance it connects to is licensed under the per-processor model.

Deprecated/Removed Command Line Utilities of SQL Server 2005

Tool Purpose Status

isql Used to execute SQL/SP/Script from Command Prompt Removed

rebuildm This utility was used was used to rebuild the master database. Removed

regrebld This utility was used to back up and restore the Removed
SQL Server Registry entries.

sqlmaint This utility is used to execute maintenance plans that were Deprecated
created in previous versions of SQL Server.

readpipe This utility is used to verify a client’s connectivity to SQL Server Deprecated
through named pipes

Osql Used to execute SQL/SP/Script from Command Prompt Deprecated

makepipe This utility is used to verify a client’s connectivity Deprecated
to SQL Server through named pipes

SQL Server Express 4GB Size Limitation and the Error

I have seen lot of queries regarding the size limitation in SQL Server Express. We all know there is limitaiton and its 4GB. But is it exactly 4GB or when the engine stops you pumping more data into the database? And whats the error you gets? These are the few FAQ on this subject. Then I thought I should try myself and document it.

SQL Server Version

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


(a) When We try to restore a database of size more than 4 GB

RESTORE FILELISTONLY FROM DISK='D:\GP.bak'
RESTORE DATABASE GPtogpx
FROM DISK='D:\GP.bak'
WITH MOVE'Gp' TO 'd:\GP.mdf',
MOVE 'Gp_log' TO 'd:\GP_log.ldf'

Error
Msg 1827, Level 16, State 4, Line 1
CREATE DATABASE or ALTER DATABASE failed because the
resulting cumulative database size would exceed your licensed
limit of 4096 MB per database. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

(b) When a database crosses 4 GB Limit

sp_spaceused Result

database_name database_size unallocated space
-------------------- ------------------------------------
ABC 4216.75 MB 77.16 MB

reserved data index_size unused
------------------ ------------------ ------------------
4115296 KB 4098792 KB 11824 KB 4680 KB

Error

Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'GPtogpx' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


So, the bottom line of this study is, the limitation is just not in the paper and it really going to hit you sooner or later if the database going to grow beyond 4GB. So go for Licensed Edition

How to upgrade SQL server 2005

use SKUUPGRADE=1 swtich from the command prompt with setup.

Eg :
start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

Refer
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade

Evaluation to EE Upgrade
Refer :
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2111140&SiteID=1

Troubleshooting Performance Problems in SQL Server 2005

Good resource on Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Msg 3154, Level 16, State 4, Server YourDatabaseName, Line 1

This error tells you that the database being restored is already existing in the server. Use WITH REPLACE or Change the database Name which you are restoring.

RESTORE DATABASE YOURDATABASENAME FROM DISK='D:\YOURDB.BAK' WITH REPLACE

FAQ : SQL Server Certifications

The New Generation of Microsoft Certifications http://www.microsoft.com/learning/mcp/newgen/default.asp

Microsoft Next Generation of Certifications: Frequently Asked Questions http://www.microsoft.com/learning/mcp/newgen/faq/

Microsoft Certifications for IT Professionals

http://www.microsoft.com/learning/mcp/mcitp

MCITP Database Developer

1. SQL Server MCTS (70-431)
2. 70-441 (Designing DB Solution)
3. 70-442 (Designing and Optimizing Data Access

MCITP DB Administrator

1. 70-431 (MCTS)
2. 70-443 Designing a DB Server Infrastructure
3. 70-444 Optimizing and Maintaining a Database Administration Solution

Or If you are upgrading from MCDBA

1. 70-431(MCTS )
2. 70447 ( Database Administration by Using Microsoft SQL Server 2005 )

MCITP: Business Intelligence Developer

1. MCTS: SQL Server 2005 (70-431) +
2. 70-445: PRO: Designing Business Intelligence Solutions by Using Microsoft SQL Server 2005 Analysis Services +
3. 70-446: PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005
   
   
   

SQL Server 2005 Service Pack and hot fixes

single window for all Service packs and other hot fixes of SQL Server 2005.

http://support.microsoft.com/kb/913089/

Next version of BPA forSQL Server 2005

The new version of the Best Practices Analyzer from MSDN Downloads.

Bookmark Lookup Logical Operation removed from SQL Server 2005

The Bookmark Lookup logical operation was removed in SQL Server 2005 and replaced with clustered index seek and RID lookup. Previous versions of SQL Server displayed the Bookmark Lookup icon but actually performed the clustered index seek and RID lookup.

patterns & practices Performance Testing Guidance project Community site.

Perfect resource for Performance Testing. Worth to read and implement. Atleast gothrough once ,stuffed with lot of information.

http://www.codeplex.com/PerfTesting

Few SQL OS DMVs Query- Courtesy to Slava

Taken from Slava Oks's Blog.
http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx


sys.dm_os_schedulers
Q. Do I need to by more CPUs?
In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:



select

AVG (runnable_tasks_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'



Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.



Q. What is affinity of my schedulers to CPUs?


select

scheduler_id,

CAST (cpu_id as varbinary) AS scheduler_affinity_mask

from

sys.dm_os_schedulers



Does my machine have either hard or soft NUMA configuration enabled?


select

CASE count( DISTINCT parent_node_id)

WHEN 1 THEN 'NUMA disabled'

ELSE 'NUMA enabled'

END

from

sys.dm_os_schedulers

where parent_node_id <> 32



Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if

A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)

B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)



select

AVG (work_queue_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'



Q: Is my system I/O bound?
You can answer this question by monitoring length of I/O queues.



select

pending_disk_io_count

from

sys.dm_os_schedulers



If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.

Connecting to SQL Server 2005 on Vista and Longhorn

I have seen lots of post in forums connecting to sql server 2005 on Vista. Check out this post by Protocol Team. http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx#comments

Default Trace in SQL Server 2005

When i saw *SQL Trace ID 1 was started by login "sa" * in my Error Log I was little surprised. Since i have never started any trace I was wondering what is this all about. Then i came to know that this is the default trace started which is a new feature introduced in SQL Server 2005. Then i was curious about how can i see the contents. here we go...


To See All the Trace and its location :
select * from ::fn_trace_getinfo(default)

To get the File Location
select * from ::fn_trace_getinfo(default) where Property=2

And copy paste the Value column text.

To Get the Trace Data

SELECT * FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) where starttime>'2007-10-15'

Webcast on SQL Server 2005 by Vinod

Webcast on SQL Server 2005 by Vinod

Capacity Planning Considerations – SQL Server 2005 - June 20th (2-3 PM IST)

Understanding IO and Storage system with SQL Server 2005 - June 21st (2-3 PM IST)

Performance Tuning and Troubleshooting – SQL Server 2005 - June 22th (2-3 PM IST)

SQL Server 2008 (Katmai) CTP Released

Great news... Microsoft has released CTP version of SQL Server 2008.

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

SQL Server 2005 Performance Dashboard Reports

This is a wonderful resource to have with SSMS. With SP2 giving you an window to build custom reports use these for your help to diagonize problems. Download from here
http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

SQL Server 2005 Integration Services Guided Tours

Good resource in SSIS

http://www.microsoft.com/sql/technologies/integration/tours.mspx

How to unlock the user with out password reset

there is no direct way... this is a workaround

Alter login [test1] with CHECK_POLICY = OFF
go
Alter login [test1] with CHECK_POLICY = ON
go

SQL Server 2005 Deployment Guidance for Web Hosting Environments - Best Practices

a wonderful article which explain almost all issues in SQL Server 2005

SQL Server 2005 Deployment Guidance for Web Hosting Environments - by Alex DeNeui http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/SQL2005DGWHE.mspx

FAQ : How to change Authentication Mode in SQL Server 2005

Trying to connect to SQL Server instance using SQL Server Login (mixed mode Authentication ) when the server is actually configured for Windows authentication is a very common issue for a newbie. How to change authentication mode using Management Studio is as follows :-

• Open management Studio and connect to the server
• Right Click on the Server Name and select the properties
• Select Security Page in the left pane
• On Security page there are two options
o Windows Authentication
o SQL Server and Windows Authentication Mode
• Select whichever the mode you want and apply and close (OK)
• The authentication mode is read from the registry when SQL Server first
Starts up. Therefore, if you change the authentication mode, you must stop
and restart the SQL Server service for it to take effect.

NOTE : RESTART THE SERVICE AFTER THE CHANGE

Third party Tools for SQL Server

Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object


Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare

DTS Comparison
Red Gate – DTS Package Compare

Server Comparison
Quest - ServerCompare

Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

courtesy to Arnie -- thanks Arnie

Optimizable SARGs in SQL Server

SQL Server’s Query Optimizer examines the conditions within the query’s where clause to determine which indexes are actually useful. If SQL Server can optimize the where condition using an index, the condition is referred to as a search argument or SARG for short. However, not every condition is a “sargable” search argument:

✦ Multiple conditions that are ANDed together are SARGs, but ORed conditions are
not SARGs.
✦ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
optimizable.
It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
✦ Conditions that begin with wildcards don’t use indexes. An index can quickly
locate Smith, but must scan every row to find any rows with ith anywhere in the
string.
✦ Conditions with expressions are not SQL Server compliant, so these expressions
will be broken down with the use of algebra to aide with the procurement of
valid input data.
✦ If the where clause includes a function, such as a string function, a table
scan is required so every row can be tested with the function applied to the
data.

Drop Database User who Owns Schema

Scenario :

UserName : GPxTestUser is owner of db_datawriter schema, and we wanted to drop the user

Once u made a user as schema owner (this is a common problem when we create Database user using SSMO, because by mistake everybody check the schema instead of Database Role) , then there is no way to revert back or change the owner of schema from "Database User" window. This can confuse a newbie. Though, I feel it should not be allowed to change the Owner of Schema from Database User Window and it should be done from "Schema Property Window" only. What i would say is this option of choosing schema owner should be removed form "Database User" window altogether to avoid confusion.

When we try to drop a database user who owns some schema from Database the system will throw the following Error :-

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

First you should see the Schemas and the curresponding owners

SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id


Drop USER gpxtestuser

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Resolution :-

Alter the Schema Owner to any other Prinicpal; here I am changin owner of DB_Datawriter as DB_Datawriter (earlier it was GPxTestUser)

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]

Then the user can be droped

To Transfer/Change the ownership of a object to another Schema.

If you want to drop a Schema , first you should transfer the object owned by this schema to another schema otherwise, when you drop schema you will get a messge as mentioned below :-

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'SCH1 ' because it is being referenced by object 'TAB1 '.

Now, before droping the schema SCH1 , the obeject(s) need to be transfered to another schema.

Syntax : ALTER SCHEMA Target_schema_name TRANSFER object_name

The below statement will transfer the Ownership of TAB1 from SCH1 to DBO

ALTER SCHEMA dbo TRANSFER SCH1.TAB1

in the same manner , transfer all the objects to whichever schema you want and then drop the schema.

Dynamic Management Views that can be used to monitor and troubleshoot performance issues in SQL Server.

Add Prefix Sys. to view

dm_exec_cached_plans : Provides information about the query execution plans that are cached by SQL Server for faster query execution.

dm_exec_connections :Provides information about the connections established to SQL Server on various endpoints.

dm_exec_sessions :Contains one row per authenticated session on the SQL Server instance.
dm_exec_query_stats :Provides aggregate performance statistics for cached query plans.

dm_exec_query_optimizer_info : Provides detailed statistics about the operation of the SQL Server query optimizer.

dm_io_pending_io_requests :Contains a row for each pending I/O in the system.

dm_os_loaded_modules : Contains a row for each module loaded into the server address space.

dm_os_memory_cache_counters :Provides a general snapshot of the health of the cache.

dm_os_memory_cache_entries : Enables you to view all entries in caches and their statistics.

dm_os_performance_counters :Lists SQL Server 2005 Performance Monitor counters and their current value.

dm_os_waiting_tasks :Provides information on the wait queue of tasks that are waiting on some resource.

dm_tran_locks :Returns one row for every active request to the lock manager that has either been granted or is waiting to be granted (that is, the request is blocked by an already granted request).

dm_tran_current_transaction :Returns a single row that displays state information of the transaction in the current session.

dm_tran_active_transactions :Provides information about active transactions.

dm_clr_properties :If the CLR is enabled, provides information such as CLR version, directory, state, and so on.

dm_clr_appdomains :Returns a row for each CLR application domain in the server.

dm_clr_loaded_assemblies :Contains a row for each CLR user assembly loaded into the server address space.

Performance Tuning - SQL Server 2005

Few best resources :
SQL Server 2005 Waits and Queues - Performance Tuning
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Database Mirroring Best Practices and Performance Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

Why SQL Server 2005 has Performance degrades after upgrade
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/09/26/compilation-time-issues-in-oltp-applications-when-upgrading-to-sql-2005.aspx

A Special Republic Day

My first republic day as a Ex- Sailor. I am no more in Delhi, so I don’t feel that warm and tension. I always believe that if you want to know the significance and true spirit Republic Day it is Delhi the place to be. I still remember, those days when I and Leena (my wife) went first time to watch republic day parade in 2002. On that day we could not make it because of high security though I was in uniform. But in the next year we could make it. The whole function will be around 3 hrs and it is always colorful, vibrant and worth to watch. It is a trailer show of the diverse India. I got a pass of enclosure just opposite to VVIP’s daises. Now all those days are gone. I am not sure I would ever watch republic day parade again from India Gate. I really miss Delhi.

SQL Server 2005 DMVs for monitoring/tuning TEMPDB

In SQL Server 2005 , TempDB plays a significant role in overall SLQ server performance. In earlier versions it was difficult to monitor and troubleshoot the bottle neck.

In SQL Server 2005, you have number of DMVs to see which users are accessing the TEMPDB database , the internal object being used and version Stores size. The DMV are as follows

Sys.dm_db_file_space_usage
Sys.dm_db_session_file_usage
Sys.dm_db_task_space_usage

Note : In SQL Server 2005 these DMVs only applicable to Tempdb

Counters to Monitor Tempdb Row versioning usage

(a) SQL Server:Transaction Object : Version Generation Rate (KB/s)
(b) SQL Server:Transaction Object : Version cleanup Rate (KB/s)

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Hide SQL Server Instance in SQL Server 2000 and SQL Server 2005

To hide SQL Server 2005 Instance

In SQL Server 2005 you can hide a specific instance while other instance continue to expose. To hide a instance steps are as follows
1. Open SQL Server Configuration Manager from Prgramfile – Microsoft SQL Server 2005 – Configuration Tool
2. In SQL Server Configuration Manager, Expand SQL Server 2005 Network Configuration,
3. Right Click on “Protocol for ” and select properties
4. In Flag pane , you can find HideInstance with No set. Change No to Yes.
5. No need to restart the service, when you pressed OK, the new setting is immediately applied and new connection will not be able to connect to the instance using SQL Browser Service. You can still connect to the instance by specifiying protocol , Server name/IP address and port number or the named pipe directly

To hide an instance of SQL Server 2000:

1. Open the SQL Server Network Utility.
2. Select the instance that you want to hide. (This is only available on SQL Server 2000 instances). 3. Select TCP/IP from the list of enabled protocols.
4. Click Properties.
5. Select the Hide Server check box.
6. Click OK.
Note : Hide Server Option Cannot Be Used on Multiple Instances of SQL Server 2000
Refer :http://support.microsoft.com/kb/308091

Apply Command : New in SQL Server 2005

Apply command can be used with a table-valued user defied function (UDF) so that the UDF accepts a different parameter value for each corresponding row being processed by the main query. There are two ways to apply, apply command.
(a) Cross Apply :- It is bit confusing because it has a contradictory behavior from name. ie. it operates more like an inner join tan a cross join. The Cross Apply command will join data from the main query with any table-valued data sets from the UDF. If no data is returned from the UDF, then the row from the main query is also not returned. So, its just behave like inner join
(b) Outer Apply :- It will return all the rows from main query , if at all no data is returned from the UDF. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

It is well explained in BOL with perfect example. But thing is that you must know something like this exists in SQL Server 2005.

MCITP SQL 2005 DBA - Yet another mile stone in my SQL journey

I am a MCITP in SQL 2005 track from 6 Jan 07. The exam was no. 70-447 and I scored 918 out of 1000. I was little late to achieve this goal as per my schedule , but at the end of the day I am more than happy now. By getting certified one does not become a master , but it of course prove that he/she has a learning habit and if u have done in the true spirit u will have fare understanding about the tools and the new features of the product. So, I would always recommend to get certified as soon as possible. The exam was not easy as I thought. The question pattern has totally changed. This is my 9 th Microsoft certification, so I took this exam for granted. But when I saw the questions and the new case study section I was sweating. You have to read 4-5 papers of the Case study document and understand the language and the actual requirement. And also each case study section has its own time limit. So anybody going for 70-447 , must know this pattern change and the new case study section.

Total time – 210 min
Total Questions – 66
Pass marks - 700
Total - 1000

Blocked Process Threshold Option – A useful New Feature of SQL Server 2005

SQL Server 2005 introduces a new advanced system configuration (sp_configure) option called "blocked process threshold." We can use this option to proactively monitor blocking and deadlocking instances. You can use sp_configure to set the blocked process threshold to an integer value between 0 (the default) and 86400 (24 hours). If you have set the blocked process threshold configuration value to 15. Now if there is a blocking for over 15 seconds, SQL Server will raise an event that can be captured by using the SQL Server event notification mechanism to perform an action, and the event can also be seen in SQL Profiler. The event is raised every 15 seconds after that until the blocking is resolved.

SQL Server raises the BLOCKED_PROCESS_REPORT event when there is a blocking for 15 seconds and every 15 seconds after that until blocking is resolved. This event can be captured by using the event notification mechanism introduced in SQL Server 2005. The event notification mechanism captures the event and notifies a Service Broker service by placing a message in the queue


Reference : http://msdn2.microsoft.com/en-us/library/ms181150.aspx

Insallation of SQL Server 2005 from Local Folder

If you are installing SQL Server from CD media dumps which is copied into a local/network folder (in SQL Server 2005 Installation CDs are Two where as in DVD media only one DVD) , the folders should be named as Servers and Tools for CD1 and CD2 respectively. Otherwise , client components and SQL Server BOL will not be installed. There will be error if you are not named the folder as above mentioned.

Ref : http://msdn2.microsoft.com/en-us/library/ms144259.aspx

AUTO_UPDATE_STATISTICS_ASYNC Database Option in SQL Server 2005

SQL Server 2005 supports a new option called AUTO_UPDATE_STATISTICS_ASYNC which can be used to fine tune your database. Normally, when an executing query triggers an automatic updating of statistics through the query optimizer , the query has to wait until the statistics are updated before proceed further . in other words it can also called synchronous operation. But When AUTO_UPDATE_STATISTICS_ASYNC option is set to ON, queries do not wait for the statistics to be updated before compiling. Ie. it is an asynchronous process Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan

Note : AUTO_UPDATE_STATISTICS_ASYNC option has no effect if the AUTO_UPDATE_STATISTICS database option is OFF

Refer : http://msdn2.microsoft.com/en-us/library/ms190397.aspx

New year resolution – 2007

By the end of this year I would, no…no… I must... be a Microsoft SQL Server Developer/ DBA who possess good knowledge in Oracle also. I am planning to join for a Oracle course now. My next goal is to get certified in Oracle. And also I have learnt a lot in data warehousing area, thanks to my current project. By the end of this project , I am sure I will have a clear understanding of data warehousing process also. So hopes to get certified in Microsoft 70-446.. so more or less I have a tight schedule in this year and hopes I will be able to achieve my goals.