2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 2,100 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 4 years to get that many views.

Click here to see the complete report.

Advertisements

Stored Procedure to Get Expensive Queries

As a DBA we often need to get information on various Queries, One of them is how expensive it is. A query can be judged as expensive on various criteria’s like Long Running, Memory Utilization, CPU utilization etc.

I came up with this Stored Procedure which gives you Expensive Queries based on criteria’s like Duration, Memory, CPU, Read etc.

USE [master]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetExpensiveQueries]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_GetExpensiveQueries]
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetExpensiveQueries]
@Limit AS INT,
@Database_Name AS VARCHAR(255),
@Expense_Counter INT
AS
/*Variables Used
1. @Limit — No of Recoreds to be Retrieved in Int
2. @Database_Name — Database Name for which the Expense queries needs to be Retrieved in Varchar
3. @Expense_Counter — Criteria on Which the Query Expense needs to judged
Refer Below for Expense Counter
End Variables Used*/
/*Expense Counter
DurTimeAvgMin 0
CPUTimeAvgMin 1
TotalCPUTime 2
TotalDurTime 3
NoPhysicalReads 4
AvgNoPhyscialReads 5
NoLogicalReads 6
AvgNoLogicalReads 7
*/
SELECT Database_name,QueryText,ProcBatTest,PlanGenerationNumber,ExecutionCount,DurTimeAvgMin,
CPUTimeAvgMin,TotalCPUTime,TotalDurTime,NoPhysicalReads,AvgNoPhyscialReads,NoLogicalReads,AvgNoLogicalReads
FROM
(
SELECT TOP (@Limit) DB_NAME(CONVERT (INT, epa.value)) AS [Database_Name],
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1,
        ((CASE eqs.statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE eqs.statement_end_offset
         END – eqs.statement_start_offset)/2) + 1) AS QueryText,
est.text AS ProcBatTest,        
eqs.plan_generation_num AS PlanGenerationNumber,
eqs.execution_count AS ExecutionCount,
(eqs.total_worker_time/1000) AS TotalCPUTime,
(((eqs.total_worker_time/1000)/eqs.execution_count)/3600) AS CPUTimeAvgMin,
(eqs.total_elapsed_time/1000) AS TotalDurTime,
(((eqs.total_elapsed_time/1000)/eqs.execution_count)/3600) AS DurTimeAvgMin,
eqs.total_physical_reads AS NoPhysicalReads,
(eqs.total_physical_reads/eqs.execution_count) AS AvgNoPhyscialReads,
eqs.total_logical_reads AS NoLogicalReads,
(eqs.total_logical_reads/eqs.execution_count) AS AvgNoLogicalReads,
eqs.last_execution_time AS LastExecutionTime
FROM SYS.DM_EXEC_QUERY_STATS eqs
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) est
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(plan_handle) eqp
CROSS APPLY SYS.DM_EXEC_PLAN_ATTRIBUTES(eqs.plan_handle) epa
WHERE attribute = ‘dbid’ 
AND DB_NAME(CONVERT (INT, epa.value)) = @Database_Name) x
–and qs.last_execution_time > ‘2011-08-09 17:29:33.750’
–If we want to get queries executed greater than some time
–and (((qs.total_elapsed_time/1000)/qs.execution_count)/3600) >= 2
ORDER BY
–Seems to be Problem with Order By working on the same
–Order By Fixed
CASE
WHEN @Expense_Counter = 0 THEN DurTimeAvgMin
WHEN @Expense_Counter = 1 THEN CPUTimeAvgMin
WHEN @Expense_Counter = 2 THEN TotalCPUTime
WHEN @Expense_Counter = 3 THEN TotalDurTime
WHEN @Expense_Counter = 4 THEN NoPhysicalReads
WHEN @Expense_Counter = 5 THEN AvgNoPhyscialReads
WHEN @Expense_Counter = 6 THEN NoLogicalReads
WHEN @Expense_Counter = 7 THEN AvgNoLogicalReads
END DESC
GO

Stored Procedure to Purge MSDB Job History

Purging your MSDB Job History is sometime very important which helps you read the Jos history in GUI very easily and keeps which only required number of days. You can add this in your Maintenance Plan or using a SSIS Package. I found one of the way a Stored Procedure to do the same. This Procedure accepts a Parameter @DaysToPurge. This is the number of days back for which you want to keep the History. I have used the existing MS SP’s for this tasks.

 

–Drop stored procedure if it already exists
USE [YourDatabaseName]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_PurgeMSDBHistory]’)
AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_PurgeMSDBHistory]
GO
–Create Procedure
–Parameter @DaysToPurge int — This is the number of days back data to be kept
CREATE PROCEDURE usp_PurgeMSDBHistory
@DaysToPurge as int
AS
BEGIN
DECLARE @VarDate DATETIME
DECLARE @DTP as INT
SET @DTP = @DaysToPurge
SELECT @VarDate = CAST((GETDATE() – @DTP) AS DATETIME)
EXEC MSDB.DBO.sp_delete_backuphistory @VarDate
EXEC MSDB.DBO.sp_purge_jobhistory  @oldest_date=@VarDate
EXECUTE MSDB..sp_maintplan_delete_log NULL,NULL,@VarDate
END
GO

Thanks and Regards,

Manjunath C Bhat.

Suggestions and comments are always welcome.

T-SQL to get the SQL Server Service Start time and Up time of SQL Server service

There are number of occasions where we need to get to know the SQL Server service start date time and also from what time the SQL Services are up and running generally termed as Uptime. There are many ways to do it like check for temp DB Creation time, using DMF sys.dm_io_virtual_file_stats etc. Below written is small T-SQL script but very useful which provides you this information. Hope this is useful. Comments and suggestions are always welcome.

USE MASTER
GO
SET NOCOUNT ON
GO
SELECT 'SQL server started at ' + 
CAST((CONVERT(DATETIME, sqlserver_start_time, 126)) AS VARCHAR(20))
+' and is up and running from '+
CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5)) 
+ ' hours and ' +
RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2) 
+ ' minutes' AS [Start_Time_Up_Time] FROM sys.dm_os_sys_info
GO
SET NOCOUNT OFF
GO 

T-SQL to get various details on currently running Active processes on SQL Server Instance

Couple of day back i was working on something very important which will run some stored procedures to accomplish the task. Now there can be many transactions or different piece of codes in a Stored Procedure. Now just think that you come across a weird situation where you are not getting your results on time or may be its taking more time than expected. Now you can determine which is session and spid your procedure is running, but what if you want to know which piece of code is running which might me causing the issue, since how long it is running etc these kind of various questions comes to mind and we are stuck. Now thanks to DMV’s and DMF’s which makes our task easier by giving various piece of information which will be very useful for us. So i came with a small piece of TSQL which will answer these kind of questions and gives us the vital information.

This also provides information on estimated completion time, completed percentage for various operations like Backup,Database Integrity Check, Index Re-Organise etc.

USE MASTER
GO
SELECT SPID,ER.percent_complete,
/* This piece of code has been taken from article. Nice code to get time criteria's
http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx
*/
      CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
        + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */   
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
        ((CASE ER.statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE ER.statement_end_offset
         END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
ORDER BY CPU DESC

Thanks & Regards,

Manjunath C Bhat

TSQL to get All Primary Key Columns and its relevant details

I was working on database design stuff and came across a situation where in i had to get all the primary keys existing in database for all tables and the primary key columns and column details. It would be very had and insane to go into each table and search for PK and its data. So i came up with this small script which gives you all information on Primary Keys. This can be modified as per your needs to get more information or for foreign keys etc. Hope this is useful for some guys looking for it. It was very helpful for me though 🙂

SELECT SS.NAME AS [TABLE_SCHEMA], ST.NAME AS [TABLE_NAME]
     , SKC.NAME AS [CONSTRAINT_NAME], SC.NAME AS [CONSTRAINT_COLUMN_NAME],
     CAST(STY.NAME AS VARCHAR(20)) +'('+
     CAST(CASE ST.NAME
     WHEN 'NVARCHAR' THEN (SELECT SC.MAX_LENGTH/2)
     ELSE (SELECT SC.MAX_LENGTH)
     END AS VARCHAR(20)) +')' AS [DATA_TYPE]
  FROM SYS.KEY_CONSTRAINTS AS SKC
  INNER JOIN SYS.TABLES AS ST
    ON ST.OBJECT_ID = SKC.PARENT_OBJECT_ID
  INNER JOIN SYS.SCHEMAS AS SS
    ON SS.SCHEMA_ID = ST.SCHEMA_ID
  INNER JOIN SYS.INDEX_COLUMNS AS SIC
    ON SIC.OBJECT_ID = ST.OBJECT_ID
   AND SIC.INDEX_ID = SKC.UNIQUE_INDEX_ID
  INNER JOIN SYS.COLUMNS AS SC
    ON SC.OBJECT_ID = ST.OBJECT_ID
   AND SC.COLUMN_ID = SIC.COLUMN_ID
  INNER JOIN SYS.TYPES AS STY
      ON SC.USER_TYPE_ID = STY.USER_TYPE_ID
  ORDER BY TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME;

Happy Coding.

Thanks and Regards,

Manjunath C Bhat

SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role

/* SQL Script to Create Roles, Schemas and users to the same */
–Creation of Database Role and Adding User’s begin
USE [YOURDATABASENAME]
GO
CREATE ROLE [YOURROLENAME]
GO
      –Adding Users Begin
      EXEC sp_addrolememberN’YOURROLENAME’, N’USERNAME1′
      GO
      EXEC sp_addrolememberN’YOURROLENAME’, N’USERNAME2′
      GO
      –Adding Users End
      –Similarly Multiple users can be added using EXEC sp_addrolemember
–Creation of Database Role and Adding User’s End
–Creation of Schema Starts
USE [YOURDATABASENAME]
GO
CREATE SCHEMA [YOURSCHEMANAME] AUTHORIZATION [YOURSCHEMANAME]
GO
–Creation of Schema End
–Association of Created Schema to Role Starts
USE [YOURDATABASENAME]
GO
ALTER AUTHORIZATION ON SCHEMA::[YOURSCHEMANAME] TO [YOURROLENAME]
GO
–Association of Created Schema to Role End