Concatenating row values in T-SQL

January 7, 2012 Leave a comment

Concatenating column values from multiple rows.
Northwind, as always :)
From this

to this
 

WITH CTE ( CategoryId, product_list, product_name, length )
AS ( SELECT CategoryId, CAST( ” AS VARCHAR(MAX) ), CAST( ” AS VARCHAR(MAX) ), 0
FROM Northwind..Products
GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( product_list +
CASE WHEN length = 0 THEN ” ELSE ‘, ‘ END + ProductName AS VARCHAR(MAX) ),
CAST( ProductName AS VARCHAR(MAX)), length + 1
FROM CTE c
INNER JOIN Northwind..Products p
ON c.CategoryId = p.CategoryId
WHERE p.ProductName > c.product_name )
SELECT CategoryId, product_list
FROM ( SELECT CategoryId, product_list,
RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
FROM CTE ) D ( CategoryId, product_list, rank )
WHERE rank = 1 ;

Categories: T-SQL

backup progress check

January 3, 2012 Leave a comment

select percent_complete,
(estimated_completion_time / 1000 / 60) estimated_time, db_name(database_id)
from sys.dm_exec_requests
where command like ‘%backup%’

Categories: Uncategorized

Get Memory Check with CLR

January 3, 2012 Leave a comment

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Management
Imports System.Diagnostics
Imports System.Data.Sql
Imports System.Collections
Imports System.Runtime.InteropServices
Imports System.Threading

Partial Public Class StoredProcedures
_
Public Shared Sub Performance()
Dim record As New SqlDataRecord(New SqlMetaData(“ProcessGroup”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“ProcessID”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“ProcessName”, SqlDbType.NVarChar, 200), _
New SqlMetaData(“Memory Usage”, SqlDbType.NVarChar, 200))

SqlContext.Pipe.SendResultsStart(record)

Dim ps() As Process

ps = Process.GetProcesses()
Dim p As Process
For Each p In ps
If p.ProcessName.Contains(“sqlservr”) Then
record.SetString(0, “SqlServer Current instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)
ElseIf p.ProcessName.ToLower.Contains(“sqlagent”) Then
record.SetString(0, “SqlServer Agent Current instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

ElseIf p.ProcessName.Contains(“sqlbrowser”) Or _
p.ProcessName.Contains(“sqlwriter”) Or _
p.ProcessName.Contains(“ReportingServicesService”) Or _
p.ProcessName.Contains(“msdtc”) Or _
p.ProcessName.Contains(“msftesql”) Or _
p.ProcessName.Contains(“msmdsrv”) Then
record.SetString(0, “Other processes associated with current sql server instance”)
record.SetString(1, p.Id.ToString)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

ElseIf p.ProcessName.ToLower.Contains(“idle”) Then
record.SetString(0, “Idle”)
record.SetString(1, p.Id)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

Else
record.SetString(0, “Other”)
record.SetString(1, p.Id)
record.SetString(2, p.ProcessName)
record.SetString(3, p.WorkingSet64.ToString)

End If
‘ Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record)
Next

SqlContext.Pipe.SendResultsEnd()

End Sub

End Class

assembly_bits

http://msdn.microsoft.com/en-us/library/ms189524%28v=SQL.90%29.aspx

ALTER DATABASE test SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Management] AUTHORIZATION [dbo]
FROM ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll’
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [PRF]
AUTHORIZATION [dbo]
FROM 0x…….
WITH PERMISSION_SET = UNSAFE;

GO
CREATE PROCEDURE [dbo].[Performance]
AS EXTERNAL NAME [PRF].[PRF.StoredProcedures].[Performance];
USE TEST;
exec performance;

Categories: Uncategorized

Resource Wait Types

January 3, 2012 Leave a comment

–//////////////////////////////////////////////////////////////////////////////////////////////////////////
– Resource Wait Types
–//////////////////////////////////////////////////////////////////////////////////////////////////////////

– Memory Waits———————————————————————————————
when @wait_type in (N’RESOURCE_SEMAPHORE’, N’CMEMTHREAD’, N’SOS_RESERVEDMEMBLOCKLIST’) then N’Memory’

– Disk I/O Waits——————————————————————————————-
when @wait_type LIKE ‘PAGEIOLATCH_%’
OR @wait_type IN (‘IO_COMPLETION’, ‘ASYNC_IO_COMPLETION’,’WRITELOG’ ) then ‘Disk I/O Waits’

–Blocking Waits——————————————————————————————–
when @wait_type LIKE ‘LCK_%’ then ‘Blocking Waits’
–CPU——————————————————————————————————-
when @wait_type in (‘CXPACKET’, ‘SOS_SCHEDULER_YIELD’) then ‘CPU’

–Network Waits———————————————————————————————
when @wait_type in (‘ASYNC_NETWORK_IO’, ‘DBMIRROR_SEND’) then ‘Network Waits’

–//////////////////////////////////////////////////////////////////////////////////////////////////////////
– System Waits
when @wait_type IN (‘FT_IFTS_SCHEDULER_IDLE_WAIT’,’LOGMGR_QUEUE’,’CHECKPOINT_QUEUE’,
‘SLEEP_TASK’,’BROKER_TO_FLUSH’,’LAZYWRITER_SLEEP’,’REQUEST_FOR_DEADLOCK_SEARCH’,
‘SQLTRACE_BUFFER_FLUSH’,’XE_TIMER_EVENT’) then ‘System Waits’
–//////////////////////////////////////////////////////////////////////////////////////////////////////////
– Preemptive Wait Types
when @wait_type LIKE ‘PREEMPTIVE%’ then ‘Preemptive Wait Types’

Categories: Performance, T-SQL

long-running queries

January 3, 2012 Leave a comment
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
--, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC
Categories: Performance, T-SQL

Reclaim Space using DBCC CLEANTABLE

December 31, 2011 Leave a comment

Client asked for advice when his portal database, 

image

grown to half TB,  became unmanageable.

Looking at sys.dm_db_index_physical_stats

gave the following:

image

taking into account that rebuilding the indexes is this case could be very timely resource-intensive operation,

DBCC CLEANTABLE

-----------------------------------------------------------------------------
-- Reclaim space from dropped variable length columns and text columns.      
-----------------------------------------------------------------------------

RAISERROR('Reclaiming space from dropped variable length columns and text columns...', 10, 1) WITH NOWAIT

DECLARE @TableName  sysname       = ''
DECLARE @SchemaName sysname       = ''

DECLARE cur CURSOR FOR 
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName' 
         , name                     AS 'TableName'
      FROM sys.tables
     WHERE is_ms_shipped = 0
       AND type_desc     = 'USER_TABLE'
  ORDER BY 'SchemaName', 'TableName'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName        

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TableName = @SchemaName + '.' + @TableName
    RAISERROR('   Examining table %s', 10, 1, @TableName) WITH NOWAIT
    DBCC CLEANTABLE(0, @TableName, 100000) WITH NO_INFOMSGS
    FETCH NEXT FROM cur INTO @SchemaName, @TableName        
END

CLOSE cur
DEALLOCATE cur

RAISERROR('Done.', 10, 1) WITH NOWAIT
Categories: Performance

Query to get max row size

December 30, 2011 Leave a comment
SELECT OBJECT_NAME (sc.[id]) tablename
, COUNT (1) nr_columns
, SUM (sc.length) maxrowlength
FROM syscolumns sc
join sysobjects so
on sc.[id] = so.[id]
WHERE so.xtype = 'U'
GROUP BY OBJECT_NAME (sc.[id])
ORDER BY SUM (sc.length) desc
Categories: Performance
Follow

Get every new post delivered to your Inbox.