Order by Month Number and Show Month Name

This may feel very simple to many developers and yes it is, but I saw this request somewhere in community website.So thought of creating a very simple example. 

Here I am using AdventureWorks2014 database, we have table [AdventureWorks2014].[HumanResources].[Employee] and we will use [HireDate] column. I am adding two new columns named HiredMonthName and HiredMonthNumber to make our example clear and simple to understand. As the name shows HiredMonthName will show the month name from [HireDate] and HiredMonthNumber will be showing the month number from [HireDate]. I am using two date functions DatePart and DateName to get the month number and name respectively. As our requirement we need our resultset  to be sorted by month number but it should display month name. Here I am using both columns in resultset. You can remove HiredMonthNumber if you wish to. Here is the script and sample output.

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[HireDate]

      ,DateName(Month,[HireDate]) as 'HiredMonthName'

      ,DatePart(Month,[HireDate]) as 'HiredMonthNumber'

      ,[OrganizationNode]

      ,[OrganizationLevel]

      ,[JobTitle]

      ,[BirthDate]

      ,[MaritalStatus]

      ,[Gender]

      ,[SalariedFlag]

      ,[VacationHours]

      ,[SickLeaveHours]

      ,[CurrentFlag]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks2014].[HumanResources].[Employee]

  Order By HiredMonthNumber ASC

Sql Server – Listing all tables Metadata (Table Size, row count, Index Size)

Sql Server – Listing all tables Metadata (Table Size, row count, Index Size)

Suppose you need to find out Table size, row count , index size etc. for a table. In this case the easiest way to do that is using sp_spaceused, here is the syntax –

EXEC sp_spaceused 'TableName' Like EXEC sp_spaceused 'DimCustomer'

The output will be something like that –

name	rows	reserved	data	index_size	unused
ItemFlatten	259458	251464 KB	109072 KB	141880 KB	512 KB

Looks good and easy but what if you want the list of all tables in your database and their metadata. Here is the script for that -

Begin

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

  /*Then it exists*/

  DROP TABLE #Temp

select O.name as 'TableName',O.type_desc,SUM (reserved_page_count)  as 'ReservedPages',

SUM (used_page_count) as 'Usedpages',

SUM (

                     CASE

                           WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

                           ELSE 0

                     END

                     ) as Pages,

                     SUM (

                     CASE

                           WHEN (index_id < 2) THEN row_count

                           ELSE 0

                     END

                     ) as Row_Count

                     Into #Temp

from Sys.objects O

Inner join sys.dm_db_partition_stats S on O.object_id=S.object_id and O.type in ('U')

Group By O.name,O.type_desc

 SELECT

              name = TableName,

              type_desc,

              rows = convert (char(20), Row_Count),

              reserved = LTRIM (STR (ReservedPages * 8, 15, 0) + ' KB'),

              data = LTRIM (STR (Pages * 8, 15, 0) + ' KB'),

              index_size = LTRIM (STR ((CASE WHEN Usedpages > Pages THEN (Usedpages - Pages) ELSE 0 END) * 8, 15, 0) + ' KB'),

              unused = LTRIM (STR ((CASE WHEN ReservedPages > Usedpages THEN (ReservedPages - Usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

              From #Temp

              Order By TableName

              Drop Table #Temp

End

Output will be something like this –

Please share your feedback and let me know if you feel this can be improved in anyway.

Sql Server - Top Slowest Queries or Resource Extensive Queries

Top Slowest Queries or Resource Extensive Queries

Whenever your database is facing any performance issue or resource scarcity, the first step should be finding the culprits.

I mean finding the slowest or most resource consuming queries and usually 10% queries are responsible for 90% of problems. You can use this very simple query to get the list of those scripts and start your work to optimize their performance or other decision making like adding more memory, CPU, partitioning or any other optimization approach.

You can change Order By clause as per your requirement or use MS excel to play with data.

SELECT TOP 10 

    total_worker_time/execution_count AS 'Avg_CPU_Time', 

    T.text AS 'Sql_Script',

    execution_count,  

    plan_generation_num,  

    last_execution_time,    

    total_worker_time,  

    last_worker_time,  

    min_worker_time,  

    max_worker_time, 

    total_physical_reads,  

    last_physical_reads,  

    min_physical_reads,   

    max_physical_reads,   

    total_logical_writes,  

    last_logical_writes,  

    min_logical_writes,  

    max_logical_writes   

        

FROM sys.dm_exec_query_stats AS Q 

CROSS APPLY sys.dm_exec_sql_text(Q.sql_handle) AS T 

ORDER BY Avg_CPU_Time DESC;

Please provide your feedback for improvement.

Sql Server - Find and Replace a value across all tables in database

Find and Replace a value across all tables in database

In my project I faced this problem due to typing mistakes.

End user has entered ‘goodmorning’ and ‘good morning’ interchangeably and it was causing logic failure at application level. So task was to replace ‘goodmorning’ with ‘good morning’ in database wherever it is there. So I came up with this simple script. It worked for me but still there is room for improvement. Please feel free to suggest improvements, use it and share it.

Begin

Set NOCount on

SET ANSI_WARNINGS  OFF

Declare @TotalRec int

Declare @Count int

Declare @TableName varchar(256)

Declare @ColumnName varchar(256)

Declare @RowNum int

Declare @SqlScript varchar(999)

Declare @SearchThis nvarchar(256)

Declare @ReplaceWith nvarchar(256)

Set @SearchThis='AAron'

Set @ReplaceWith='aaRon'

Declare  @TableColumn Table

(

RowNum int Identity(1,1),

TableName varchar(256),

ColumnName varchar(256)

)

Create Table #TableColumn1

(

RowNum int Identity(1,1),

TableName varchar(256),

ColumnName varchar(256)

)

Insert into @TableColumn

select distinct T.TABLE_NAME --as 'TableName'

,Col.COLUMN_NAME --as 'ColumnName'

from

INFORMATION_SCHEMA.COLUMNS Col with (nolock)

inner join INFORMATION_SCHEMA.TABLES T  with (nolock) on T.TABLE_NAME=Col.TABLE_NAME and T.TABLE_TYPE='BASE TABLE'

where  DATA_TYPE IN ('char', 'varchar', 'nchar', 'nVarChar','text')  and T.TABLE_SCHEMA='dbo' and T.TABLE_TYPE='BASE TABLE'

Set @TotalRec=(select count(*) from @TableColumn)

Set @Count=0

while @TotalRec>@Count

Begin

Set @Count=@Count+1

Set @TableName=(Select top 1 TableName From @TableColumn T where T.RowNum=@Count)

Set @ColumnName=(Select top 1 ColumnName From @TableColumn T where T.RowNum=@Count)

 Begin Try

 EXEC ( 'Update '+@TableName+ ' Set '+ @ColumnName+'='+''''+@ReplaceWith+''''+ ' Where '+@ColumnName+' = '+''''+@SearchThis+'''')

         End Try

         Begin catch

         End catch

End

Drop table #TableColumn1

SET ANSI_WARNINGS ON;

End

Sql Server - Monitoring the Performance of All Stored Procedures

Monitoring the Performance of All Stored Procedures 

Every Project suffer from this problem, over the time they have developed many stored procedure and their versions. No one is actually sure about their utilization and performance. Use this very simple SQL Script and generate a report with all the information you required. You can modify the script as per your need like order by execution_count so you can identify most used ones or use a date range in filter criteria to narrow down your search.  

SELECT

O.Name As 'StoredProcName',

O.object_id,

ps.execution_count,

CAST(ROUND((ps.lASt_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Last_elapsed_time_In_Sec',

ps.lASt_execution_time as 'Last_execution_time',

ps.lASt_logical_reads as 'Last_logical_reads',

ps.lASt_logical_writes as 'Last_logical_writes',

ps.lASt_physical_reads as 'Last_physical_reads',

CAST(ROUND((ps.lASt_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Last_worker_time_In_Sec' ,

CAST(ROUND((ps.max_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Max_elapsed_time_In_Sec',

ps.max_logical_reads,

ps.max_logical_writes,

ps.max_physical_reads,

CAST(ROUND((ps.max_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Max_worker_time_In_Sec',

CAST(ROUND((ps.min_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Min_elapsed_time_In_Sec' ,

ps.min_logical_reads,

ps.min_logical_writes,

ps.min_physical_reads,

CAST(ROUND((ps.min_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Min_worker_time_In_Sec',

CAST(ROUND((ps.total_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Total_elapsed_time_In_Sec' ,

ps.total_logical_reads,

ps.total_logical_writes,

ps.total_physical_reads,

CAST(ROUND((ps.total_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Total_worker_time_In_Sec'

FROM Sys.objects O left join sys.dm_exec_procedure_stats ps on O.name=OBJECT_NAME(ps.object_id, ps.databASe_id)

WHERE O.TYPE='P'

ORDER BY O.Name

Feel free to use it or share it.

Please provide your feedback or suggestion to improve our work.

Thanks & Happy Scripting 

Sql Server - Monitoring the Performance of All Stored Procedures

Monitoring the Performance of All Stored Procedures 

Every Project suffer from this problem, over the time they have developed many stored procedure and their versions. No one is actually sure about their utilization and performance. Use this very simple SQL Script and generate a report with all the information you required. You can modify the script as per your need like order by execution_count so you can identify most used ones or use a date range in filter criteria to narrow down your search.  

SELECT 

O.Name,

O.object_id,

ps.execution_count,

CAST(ROUND((ps.lASt_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'LASt_elapsed_time_In_Sec',

ps.lASt_execution_time,

ps.lASt_logical_reads,

ps.lASt_logical_writes,

ps.lASt_physical_reads,

CAST(ROUND((ps.lASt_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'LASt_worker_time_In_Sec' ,

CAST(ROUND((ps.max_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Max_elapsed_time_In_Sec',

ps.max_logical_reads,

ps.max_logical_writes,

ps.max_physical_reads,

CAST(ROUND((ps.max_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'max_worker_time_In_Sec',

CAST(ROUND((ps.min_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Min_elapsed_time_In_Sec' ,

ps.min_logical_reads,

ps.min_logical_writes,

ps.min_physical_reads,

CAST(ROUND((ps.min_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Min_worker_time_In_Sec',

CAST(ROUND((ps.total_elapsed_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Total_elapsed_time_In_Sec' ,

ps.total_logical_reads,

ps.total_logical_writes,

ps.total_physical_reads,

CAST(ROUND((ps.total_worker_time/1000000.00),4)AS NUMERIC(8,4)) AS 'Total_worker_time_In_Sec'

FROM Sys.objects O left join sys.dm_exec_procedure_stats ps on O.name=OBJECT_NAME(ps.object_id, ps.databASe_id)

WHERE O.TYPE='P'

ORDER BY O.Name

Feel free to use it or share it.

Please provide your feedback or suggestion to improve our work.

Thanks & Happy Scripting