Counter of Festivals

Ashok Blog for SQL Lovers

Friday, 11 April 2014

Script To Check Last Full Backup Duration of All Database

Script To Check Last Full Backup Duration of All Database:

set nocount on
go
if exists ( select name from tempdb..sysobjects where name like '#DatabasesBackupsDuration%')
drop table #DatabasesBackupsDuration
go
create table #DatabasesBackupsDuration
(
ServerName varchar(100) null,
DBName varchar(100) null,
RecoveryModel varchar(100) null,
LastFullbackup datetime null,
FullbackupDurationSec bigint null,
DBStatus varchar (100) null,

)
go
insert into #DatabasesBackupsDuration(ServerName,DBName)
select convert(varchar,serverproperty('ServerName')),a.name 
from master.dbo.sysdatabases a
where a.name <> 'tempdb'

update #DatabasesBackupsDuration
set LastFullbackup=b.backup_start_date
from #DatabasesBackupsDuration a,(select database_name,max(backup_start_date) backup_start_date 
from msdb..backupset  where type='D' group by database_name)b
where a.DBName=b.database_name

update #DatabasesBackupsDuration 
set RecoveryModel=convert(sysname,DatabasePropertyEx(DBName,'Recovery'))

update #DatabasesBackupsDuration 
set DBStatus=convert(sysname,DatabasePropertyEx(DBName,'Status'))

update d
set d.FullbackupDurationSec = datediff(s,backup_start_date, backup_finish_date)
from #DatabasesBackupsDuration d,(select database_name, max(backup_start_date) as backup_start_date, max(backup_finish_date) as backup_finish_date from msdb..backupset 
where type ='D' group by database_name) b where d.DBName = b.database_name
go
select * from #DatabasesBackupsDuration order by LastFullbackup
go
select CAST(SUM(FullbackupDurationSec)/60 AS varchar(100))+' Minutes' As FullBackupTimeTotal from #DatabasesBackupsDuration
go
drop table #DatabasesBackupsDuration



Wednesday, 9 April 2014

SQL Server Queries taking long time to Execute in Production:

SQL Server Queries taking long time to Execute in Production:

Query Execution in the production taking long time

one of the query in the production taking almost 40 minutes instead seconds where it used to, but interestingly the same query in the development server just took 5 seconds to execute. both the environments are same WRT user contention.
The quick steps to troubleshoot in this scenario is
  1. compare the pre-execution plans (Ctrl + L) on both the production and development.

    Note : pre-execution plans are helpful to look at the execution plan with out executing the query as executing query in production with out knowing about is not recommended in production.

    Just look at both the plans on whether they are look same or not with out going in details
    if both are not same then its due to the statistics are not same.
  2. so just check when the statistics in production are updated using the below query

    SELECT STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats
    WHERE object_id = OBJECT_ID(Sales.Customer);
      -- to check the statistics on indexes , instead sys.stats use sys.indexes

    SELECT STATS_DATE(object_id, index_id) AS statistics_update_date
    FROM sys.indexes WHERE object_id = OBJECT_ID(Sales.Customer);
  3. if these dates are too old then update the statistics using the below command
    UPDATE STATISTICS Person.Address
    check the performance of the query in production .
Note: even if the “auto create statistics” and “auto update statistics are on” , SQL Server wont create or update for each and every row modification . it will only do when it reaches certain threshold.

Monday, 7 April 2014

Introduction of Dynamic SQL

Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1):
Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)
Ref:
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2



Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.
Dynamic SQL on the client
If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = "
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.
Dynamic SQL in a stored procedure
Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
        @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
        Articles
WHERE
        ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
        @TableName VarChar(100)
AS
SELECT *
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
        @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.



Introduction to Dynamic SQL (Part 2):

Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
        @strIDs VarChar(100)
AS

SELECT *
FROM
        Products
WHERE
        ProductID in (@strIDs)

GO
Oooops! No Go.
This will work
Create Procedure Search
        @strIDs VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'

Exec ( @SQL)

GO
N.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @Alias
This will
Exec('Select UserName FROM Table as ' @Alias)
DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
        ID int NOT NULL Primary Key,
        FieldName VarChar(10)
        )
Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)
Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)
sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)
You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql
Permissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')

Select * from Authors

exec('set rowcount 0')
Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')
Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')
Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
        au_id VarChar(100),
        au_fname VarChar(100),
        au_lname VarChar(100)

)

declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)

Select * from #tempauth

Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " 
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
 @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
 Articles
WHERE
 ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS
SELECT * 
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpu
Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " 
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
 @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
 Articles
WHERE
 ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS
SELECT * 
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpuf
drop table #tempauth
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)