Thursday 18 July 2013

Perform volume maintenance tasks


To achieve best performance  in SQL server while creating databases, altering the database, adding the space manually, autogrowth, Especially while restoring the database we can achieve better performance using Instant File initialization works better, we will see how it works.

Instant Initialization is a new feature of SQL Server 2005 that is based on an NTFS feature that was added to Windows XP (and therefore is also available in Windows 2003 Server). It's a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly – no matter what the file size.

This feature is not enabled by default in Microsoft, if you need to enable this we require SQL service account or Local account SQL service

(SQLServer2005MSSQLUser$SERVERNAME$MSSQLserver) group to add to group policy.

Go to run -> GPEDIT.msc -> winowssettings->Security Settings-->Local Policy-->User Right settings-->
or

Go to run--> SECPOL-->Local Policy-->User Right settings


and add SQL servie acc to group
secpol


 
Here is an one example while creating databases where we can achieve performance while creating databases.

Now I’m going to create a database. How long does it take to create a file?

SET STATISTICS TIME ON;
 
CREATE DATABASE IFIOffTest
ON
( NAME = IFIOffTest_dat,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_dat.mdf',
 SIZE = 1000,
 MAXSIZE = 2000,
 FILEGROWTH = 100 )
LOG ON
( NAME = IFIOffTest_log,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOffTest_log.ldf',
 SIZE = 100MB,
 MAXSIZE = 300MB,
 FILEGROWTH = 20MB ) ;
GO
Statistics Time tells me:
SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 4665 ms.


Now I go to Local Security Policy and add SQL Service acc to Perform volume maintenance tasks, and restart the SQL Server service.

I create a second database.
 
SET STATISTICS TIME ON;
 
CREATE DATABASE IFIOnTest
ON
( NAME = IFIOnTest_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_dat.mdf',
    SIZE = 1000,
    MAXSIZE = 2000,
    FILEGROWTH = 100 )
LOG ON
( NAME = IFIOnTest_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\IFIOnTest_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 300MB,
    FILEGROWTH = 20MB ) ;
GO
 
What does Statistics Time give me here?
SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 659 ms.

I created two databases on the same server, the same hard drive, with the same file sizes. With IFI turned on, the database was created in 14% of the time. And that’s just with a 1,000 MB file! Imagine the time savings as your database scales up
 

Thursday 4 July 2013

Log Shipping system tables

--Loghsipping tables for info of primary, seconday and error details



select * from msdb.dbo.log_shipping_monitor_alert

select * from msdb.dbo.log_shipping_monitor_error_detail

select * from msdb.dbo.log_shipping_monitor_history_detail

select * from msdb.dbo.log_shipping_monitor_primary

select * from msdb.dbo.log_shipping_monitor_secondary

select * from msdb.dbo.log_shipping_primaries

select * from msdb.dbo.log_shipping_primary_databases

select * from msdb.dbo.log_shipping_primary_secondaries

select * from msdb.dbo.log_shipping_secondaries

select * from msdb.dbo.log_shipping_secondary

select * from msdb.dbo.log_shipping_secondary_databases
 
if you are getting threshold Alerts even after dropping database, please remove info of databases in above tables if configuration is not removed.

SQL Query to find database backup details

---Query to find the backup media set and backup meda info, size and duration information and type.

SELECT TOP 100

s.database_name,

--m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BKP_Size,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

--CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

--CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

where Backup_start_Date > '2013-06-27'

--WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY backup_start_date DESC, backup_finish_date

Query to find the DB Size,used and free space of data, log files


------------------------------Data file size----------------------------

if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')

drop table #dbsize

create table #dbsize

(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))

go

 

insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)

exec sp_msforeachdb

'use [?];

  select DB_NAME() AS DbName,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,

    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),

sum(size)/128.0 AS File_Size_MB,

sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,

SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB

from sys.database_files  where type=0 group by type'

 
/* Give you the information of  data files Size,space used and free space */

--select * from #dbsize
 go
-------------------log size--------------------------------------

  if exists (select * from tempdb.sys.all_objects where name like '#logsize%')

drop table #logsize

create table #logsize

(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))

go

 

insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)

exec sp_msforeachdb

'use [?];

  select DB_NAME() AS DbName,

sum(size)/128.0 AS Log_File_Size_MB,

sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,

SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB

from sys.database_files  where type=1 group by type'

 /* Give you the information of  Log files Size,space used and free space */
 
--- select * from #logsize

 

go

--------------------------------database free size--------------------

  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')

drop table #dbfreesize

create table #dbfreesize

(name sysname,

database_size varchar(50),

Freespace varchar(50)default (0.00))

 

insert into #dbfreesize(name,database_size,Freespace)

exec sp_msforeachdb

'use [?];SELECT database_name = db_name()

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')

    ,''unallocated space'' = ltrim(str((

                CASE

                    WHEN dbsize >= reservedpages

                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576

                    ELSE 0

                    END

                ), 15, 2) + '' MB'')

FROM (

    SELECT dbsize = sum(convert(BIGINT, CASE

                    WHEN type = 0

                        THEN size

                    ELSE 0

                    END))

        ,logsize = sum(convert(BIGINT, CASE

                    WHEN type <> 0

                        THEN size

                    ELSE 0

                    END))

    FROM sys.database_files

) AS files

,(

    SELECT reservedpages = sum(a.total_pages)

        ,usedpages = sum(a.used_pages)

        ,pages = sum(CASE

                WHEN it.internal_type IN (

                        202

                        ,204

                        ,211

                        ,212

                        ,213

                        ,214

                        ,215

                        ,216

                        )

                    THEN 0

                WHEN a.type <> 1

                    THEN a.used_pages

                WHEN p.index_id < 2

                    THEN a.data_pages

                ELSE 0

                END)

    FROM sys.partitions p

    INNER JOIN sys.allocation_units a

        ON p.partition_id = a.container_id

    LEFT JOIN sys.internal_tables it

        ON p.object_id = it.object_id

) AS partitions'

-----------------------------------

 

 

 

if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')

drop table #alldbstate

create table #alldbstate

(dbname sysname,

DBstatus varchar(55),

R_model Varchar(30))

 

--select * from sys.master_files

 

insert into #alldbstate (dbname,DBstatus,R_model)

select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases

--select * from #dbsize

 

insert into #dbsize(Dbname,dbstatus,Recovery_Model)

select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'

 

insert into #logsize(Dbname)

select dbname from #alldbstate where DBstatus <> 'online'

 

insert into #dbfreesize(name)

select dbname from #alldbstate where DBstatus <> 'online'

 

select

 

d.Dbname,d.dbstatus,d.Recovery_Model,

(file_size_mb + log_file_size_mb) as DBsize,

d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,

l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace

from #dbsize d join #logsize l

on d.Dbname=l.Dbname join #dbfreesize fs

on d.Dbname=fs.name

order by Dbname

  /* Give you the information of all databases  Data, Log files Size,space used and free space */

Wednesday 3 July 2013

Query to find text in a SP

I get request couple of times to find the SP which uses some text(may be object or  author name or modified by name or comments)..

you can take help of below queries to get the list of SP's that are using text..

Query1:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%Any_Text%'
    AND ROUTINE_TYPE='PROCEDURE'

--Replace the Any_Text with text you want and search..
or

-- Open query is the text or word we are finding in the below query.
--Below query is ised to find the text in SP

Query 2:
SELECT sys.sysobjects.name, sys.syscomments.text

FROM sys.sysobjects INNER JOIN syscomments

ON sys.sysobjects.id = sys.syscomments.id

WHERE sys.syscomments.text LIKE '%OpenQuery%'

AND sys.sysobjects.type = 'P'

ORDER BY sys.sysobjects.NAME