Jobs Running on Server

To find out jobs running on a server through T-Sql

DECLARE @job_owner VARCHAR(100);

SELECT @job_owner = SUSER_SNAME()

IF EXISTS
(
    SELECT 1    FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N'tempdb..#JobStats')
)
BEGIN
    DROP TABLE #JobStats
END


CREATE TABLE
    #JobStats
    (
        job_id UNIQUEIDENTIFIER
    ,    last_run_date INT
    ,    last_run_time INT
    ,    next_run_date INT
    ,    next_run_time INT
    ,    next_run_schedule_id  INT
    ,    requested_to_run INT
    ,    request_source INT
    ,    request_source_id VARCHAR(100)
    ,    running INT
    ,    current_step INT
    ,    current_retry_attempt INT
    ,    job_state INT
    )  


 INSERT INTO #JobStats
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
          
SELECT
    sysjobs.name
,    JobStats.current_step
,    JobStats.running
,    JobStats.*
FROM
    #JobStats JobStats
    JOIN MSDB..sysjobs sysjobs
    ON JobStats.job_id = sysjobs.job_id

OR 

SELECT

SYSJOBS.Name

, SYSJOBS.Job_Id

, SYSPROCESSES.HostName

, SYSPROCESSES.LogiName

, *

FROM

MSDB.dbo.SYSJOBS

JOIN

MASTER.dbo.SYSPROCESSES

ON

SUBSTRING(SYSPROCESSES.PROGRAM_NAME,30,34)

=MASTER.dbo.fn_varbintohexstr ( SYSJOBS.job_id)

AND

LEFT(PROGRAM_NAME,28) ='SQLAgent - TSQL JobStep (Job'

Error: it(job) does not have any job server(s) defined

Today I was creating a job on SQL SERVER 2000 through SSMS 2008, and when I was trying to run the job it gave me error as

Server: Msg 14256, Level 16, State 1, Procedure sp_start_job, Line NN

Cannot start job 'XXXX' (ID NNNN) because it does not have any job server(s) defined.

Solution:

Well the solution is pretty simple- In Job properties got to TARGET and check either 'Target local server' or 'Target multiple servers'.

Cracking Query Execution - II: Why Actual and Estimated Query Execution Plan may differ

Generally there is no difference between Actual Execution plan and Estimated plan and Query is executed the way Estimated Plan outlines.However, circumstances can arise that can cause Execution Plan to change:

Obsolete Statistics:
The main cause of difference between the plans is difference between the statistics and the actual data.This generally occurs over time as data is added and deleted. This causes the key values that define the index to change,or their distribution to change.

Estimated Plan becomes Invalid:
In some instances, SQL Server Engine doesn't even generate Estimated execution plan, like
CREATE TABLE tblAbc
(
Col1 INT,
Col2 INT);

INSERT INTO tblAnd VALUES ( 1,2);

This will through error as
Msg 208, Level 16, State 1, Line 6
Invalid object name 'tblAnd'.

As table tblAbc doesn't exists in database yet, so it cant create a plan for it.

When Parallelism is required:
When a plan meets a threshold for parallelism two plans are created. Which plan is actually executed is up to the query engine.

Cracking Query Execution - I: Query Cycle

The very basic idea of designing a database is to store information and Query effectively, I will discuss what a Query is and how it gets executed:

QUERY: A precise request for information retrieval with database and information systems

What happens when we submit a query:
As we submit a query, number of processes in the database engine go to work on it so as to manage the system in such way that retrieve or store a data in a timely a manner as possible, whilst maintaining the integrity of the data.
We can roughly break down these processes into two stages
A) Processes that occurs in the Relational engine
In relational engine the query is parsed and then processed by Query Optimizer, which generates an execution plan. This plan is sent (in binary format) to Storage engine.
B) Processes that occurs in the Storage engine
In storage engine process such as locking, index maintenance and transaction occur.


Query Parsing:
As a SQL Server Engine receives a query,it passes it through a process that checks that the T-SQL is written correctly and is well formed.The output of the parser process is a PARSE TREE.

If the T-SQL is a DML, the Parse tree is passed to a precess called as ALGEBRIZER, it resolves all the names of various objects like table and columns referred in the query, it also performs type checking and does aggreate binding (determines location of aggregates with in the query). Output of Algebrizer is called as QUERY PROCESSOR TREE or ALEGEBRISER TREE and is passed to Query optimiser
Non DML are not passed to Algebrizer of query optimiser as non-DML cant be optimized (SQL Server doesn't provide many ways of creating a table).

Query Optimizer:
The optimizer figures out how best to implement the request represented by the T-SQL query you submitted. It decides if the data can be accessed through indexes, what types of joins to use and much more. The decisions made by the optimizer are based on what it calculates to be the cost of a given execution plan, in terms of the required CPU processing and I/O, and how fast it will execute. Hece, this is known as cost-based plan.

The optimizer will generate and evaluate many plans (unless there is already a cached plan) and, generally speaking, will choose the lowest-cost plan i.e. the plan it thinks will execute the query as fast as possible and use the least amount of resources, CPU and I/O. The calulation of the execution speed is the most important calculation. Sometimes, the optimizer will select a less efficient plan if it thinks ith will take more time to evaluate many plans than to run a less efficient plan.

Once the optimizer arrives at an execution plan, the estimated execution plan is created and stored in a memory space known as the PLAN CACHE.
Sometimes actual executed plan differs from the estimated execution plan , WHY

Query Execution:
Once the execution plan is generated, the action switches to the storage engine, where the query is actually executed, according to the plan.

Difference between Stored Proc and User-defined Functions

Today morning my close friend Prashanth (DotNet expert) asked difference between Stored Proc and Functions . So I am dedicating this post to him, also today is bithday of beautiful Buddu, his girlfriend.

Difference betweem Stored Proc and User-defined Functions

• Stored Procedure have pre-compiled execuction plan where as functions do not.
• Functions are used for computations where as procedures are mainly used for performing business logic.
• Functions can only have 'in' parameter where as SP can have both 'in' and 'out' parameters.
• Function does not allow DML (insert, update, delete) queries on an object where as SP allows.
• Functions can be used inline where as SP can't be.
• Stored Procedure can retun more than one value at a time while funtion returns only one value at a time.
• Functions MUST return a value, procedures need not be.

Please leave your comments..

Functions

With my personal experience of SQL Server, I believe Function (User defined functions) are under utilized then their potential so I thought of writing this post in complete honor of UDFs.

Microsoft SQL Server provides three programmable objects
a) User defined Functions b) Stored Procedure c) Triggers

" Functions are pre-prepared pieces of code that may accept parameters, and always return a value"

UDFs support the creation of rich programming logic, including looping, flow control, decision making and branching.
UDFs can be divided into two types based on the value they return
a) Scaler functions b)Table-valued functions

A) Scaler functions: Scalar functions accept 0 or more input parameters and return a single scalar value.
You use the CREATE FUNCTION Transact-SQL statement to create a function. The general syntax of the statement is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ EXECUTE_AS_Clause ]
}

Example from AdventureWorks database
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
BEGIN
DECLARE @ret int;

SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

IF (@ret IS NULL)
SET @ret = 0

RETURN @ret
END;
GO

--Execute
SELECT
p.[ProductID]
,p.[Name]
, dbo.ufnGetStock(p.ProductID) as [Stock]
FROM Production.Product p;

Result


B) Table-Valued Functions: The only difference in table-valued functions is that they return a table as output. Therefore, they are generally used in the FROM clause of a SELECT statement and possibly joined to other tables or views.

Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <>
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

Example from AdventureWorks :
CREATE FUNCTION [dbo].[ufnGetContactInfo] (@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
[ContactID] int PRIMARY KEY NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[Email] [nvarchar] (150) Null
)
AS
BEGIN
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT
c.[ContactID]
,c.[FirstName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
WHERE c.[ContactID]= @ContactID;
END;

RETURN;
END;
GO
select * from ufnGetContactInfo(1209)
Result



C) Restrictions on UDFs
Although we can execute virtually any valid batch of code with in function, but still they do have some restrictions:
Most significant is you cannot use a function to change the state of any object in a database or the database itself.Therefore,you cannot insert, update, or delete data in tables, nor can you create, alter, or drop objects in the database. However, you can create one or more table variables and issue INSERT, UPDATE, and DELETE statements against the table variable


Please leave comment...

Views - the other face

To put it in a simple way : A view is a SELECT statement that has a name and is stored in Microsoft SQL Server.

Views act as virtual tables to provide several benefits.

a) Re-usability : A view gives developers a standardized way to execute queries, enabling them to write certain common queries once as views and then include the views in application code so that all applications use the same version of a query.

b) Security : A view provides a level of security by giving users access to just a subset of data contained in the base tables that the view is built over and can give users a more friendly, logical view of data in a database.

c) Performance : a view with indexes created on it can provide dramatic performance improvements, especially for certain types of complex queries.

We can categorize views as :
a) regular views, b) updateable views, c) indexed views.

A) Syntax of view creation
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

ENCRYPTION : Should View be encrypted
SCHEMABINDING : will not allow you to drop a table, view or function reference by this view without dropping the view
VIEW_METADATA : returns metadata about a view to client-side data access libraries.

Select statement can be of any complexity as long as it is a valid query with following exceptions

• should not have COMPUTE or COMPUTE BY clause
• should not have INTO keyword
• should not have OPTION clause
• should not reference a temporary table or table variable
• should not have ORDER BY clause unless it also specifies the TOP operator

Example of a view can be on AdventureWorks database
CREATE VIEW [vwEmployee]
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]


B) Modifying Data Through Views (UPDATEBLE VIEW)
Although views allows you to update data with some exception but it will be best practice if we use INSTEAD triggers.

Exception for data modification through VIEWS

• All changes must directly reference column, not derivations of a column
• View definition cannot contain GROUPBY or DISTINCT clause
• You cannot modify columns that are derived through aggregate functions (AVG,COUNT, SUM,MIN,SUBSTRING)
• You cannot reference columns generated by using operators such as union, crossjoin, and intersect.
• You cannot use TOP when you specify WITH CHECK OPTION

Example:
UPDATE [vwEmployee ]
SET [Phone] ='123-256-1685'
WHERE [employeeID] =1

C) INDEXED VIEW

An indexed view, also called a materialized view, causes SQL Server to execute the SELECT statement in the view definition. SQL Server then builds a clustered index on the view’s results, and stores the data and index within the database. As you change data in the base tables, SQL Server propagates these changes to the indexed view. If the result of the view could change from one execution to another or could change if different query options were set, the entire set of data SQL Server calculated and stored would be invalidated. Therefore, all the operators or functions that can cause varying results are disallowed.

Restrictions for index views

• View should be Schema bound
• The SELECT statement cannot reference other views.
• All functions must be deterministic. For example, you cannot use getdate() because every time it is executed, it returns a different date result.
• AVG, MIN, MAX, and STDEV are not allowed.

Example to create index on above view,
We will need to have it schema bound
ALTER VIEW [vwEmployee]
WITH
SCHEMABINDING
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]

Now we can create index as
CREATE UNIQUE CLUSTERED INDEX [IX_vwEmployee_EmpID] ON [vwEmployee]
(
[EmployeeID] ASC
)