Sql Server arşivleri - Oğuzhan ABALI

Sql Bağlantı Sızıntısı Tespit Etmek – Sql Connection Leaks

select count(*) as sessions,
s.host_name,
s.host_process_id,
s.program_name,
db_name(s.database_id) as database_name
from sys.dm_exec_sessions s
where is_user_process = 1
group by host_name, host_process_id, program_name, database_id
order by count(*) desc;

select count(*) as sessions,

s.host_name,

s.host_process_id,

s.program_name,

db_name(s.database_id) as database_name

from sys.dm_exec_sessions s

where is_user_process = 1

group by host_name, host_process_id, program_name, database_id

order by count(*) desc;

sorgusu ile hangi database’de ve hangi hostta ne kadar session olduğu öğrenilebilinir. Sonrasında hangi query’inin ne kadar sürdüğü konusunda şu alttaki komutta dbname ve hostname değiştirilelerek sızıntı olan query bulunabilinir.

   declare @host_process_id int = 1508;
  declare @host_name sysname = N'hostname';
  declare @database_name sysname = N'dbname';
 
  select datediff(minute, s.last_request_end_time, getdate()) as minutes_asleep,
         s.session_id,
         db_name(s.database_id) as database_name,
         s.host_name,
         s.host_process_id,
         t.text as last_sql,
         s.program_name
    from sys.dm_exec_connections c
    join sys.dm_exec_sessions s
         on c.session_id = s.session_id
   cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) t
   where s.is_user_process = 1
         and s.status = 'sleeping'
         and db_name(s.database_id) = @database_name
         and s.host_process_id = @host_process_id
         and s.host_name = @host_name
         and datediff(second, s.last_request_end_time, getdate()) > 60
   order by s.last_request_end_time;

declare @host_process_id int = 1508;

declare @host_name sysname = N'hostname';

declare @database_name sysname = N'dbname';

select datediff(minute, s.last_request_end_time, getdate()) as minutes_asleep,

s.session_id,

db_name(s.database_id) as database_name,

s.host_name,

s.host_process_id,

t.text as last_sql,

s.program_name

from sys.dm_exec_connections c

join sys.dm_exec_sessions s

on c.session_id = s.session_id

cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) t

where s.is_user_process = 1

and s.status = 'sleeping'

and db_name(s.database_id) = @database_name

and s.host_process_id = @host_process_id

and s.host_name = @host_name

and datediff(second, s.last_request_end_time, getdate()) > 60

order by s.last_request_end_time;

MSSql – Sql Server versiyonunu öğrenme

SELECT @@VERSION AS [Current Version];

1	SELECT @@VERSION AS [Current Version];

query ile öğrenilebilinir.

Distinct case insensitive sql

Örnek kullanım:

SELECT DISTINCT column_name COLLATE sql_latin1_general_cp1_cs_as FROM dbo.Users

1	SELECT DISTINCT column_name COLLATE sql_latin1_general_cp1_cs_as FROM dbo.Users

MSSQL’de Queryler ve Performansları

SELECT TOP 10 

total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
ORDER BY Avg_CPU_Time DESC

SELECT TOP 10

total_worker_time/execution_count AS Avg_CPU_Time

,execution_count

,total_elapsed_time/execution_count as AVG_Run_Time

,(SELECT

SUBSTRING(text,statement_start_offset/2,(CASE

WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offset

END -statement_start_offset)/2

) FROM sys.dm_exec_sql_text(sql_handle)

) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY Avg_CPU_Time DESC

Entity Framework 6 Working with Transactions

using (var context = new BloggingContext())

{
    using (var dbContextTransaction = context.Database.BeginTransaction())
    {
        try
        {
            context.Database.ExecuteSqlCommand(
                @"UPDATE Blogs SET Rating = 5" +
                    " WHERE Name LIKE '%Entity Framework%'"
                );
 
            var query = context.Posts.Where(p => p.Blog.Rating >= 5);
            foreach (var post in query)
            {
                post.Title += "[Cool Blog]";
            }
 
            context.SaveChanges();
 
            dbContextTransaction.Commit();
        }
        catch (Exception)
        {
            dbContextTransaction.Rollback();
        }
    }
}

using (var context = new BloggingContext())

{

using (var dbContextTransaction = context.Database.BeginTransaction())

{

try

{

context.Database.ExecuteSqlCommand(

@"UPDATE Blogs SET Rating = 5" +

" WHERE Name LIKE '%Entity Framework%'"

);

var query = context.Posts.Where(p => p.Blog.Rating >= 5);

foreach (var post in query)

{

post.Title += "[Cool Blog]";

}

context.SaveChanges();

dbContextTransaction.Commit();

}

catch (Exception)

{

dbContextTransaction.Rollback();

}

Trigger ile Loglama İşlemi

 Create TRIGGER  [dbo].[Applications_Logs_Trigger]

ON [dbo].[Applications] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
 
 SET NOCOUNT ON;
 
    DECLARE @operation as Varchar(10)
    DECLARE @Count as int
    SET @operation = 'Inserted' -- Setting operation to 'Inserted'
              SELECT @Count = COUNT(*) FROM DELETED
    if @Count > 0
        BEGIN
              SET @operation = 'Deleted' -- Set Operation to 'Deleted'
              SELECT @Count = COUNT(*) FROM INSERTED
              IF @Count > 0
              SET @operation = 'Updated' -- Set Operation to 'Updated'
        END
	if @operation = 'Deleted'
 
			BEGIN
				Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)
				SELECT GETDATE(),'SQL','Deleted','Applications',Id,NULL from deleted
			END
	ELSE
			BEGIN
				SELECT GETDATE(),'Inserted','SQL','Inserted','Applications',Id,NULL from inserted
		 if @operation = 'Inserted'
 
			BEGIN
			Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)
				SELECT GETDATE(),'SQL','Inserted','Applications',Id,NULL  from inserted
			END
	ELSE
			BEGIN
				  Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)
					SELECT GETDATE(),'SQL','Updated','Applications',Id,NULL  from inserted
			END
	END
GO

Create TRIGGER [dbo].[Applications_Logs_Trigger]

ON [dbo].[Applications] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION

SET NOCOUNT ON;

DECLARE @operation as Varchar(10)

DECLARE @Count as int

SET @operation = 'Inserted' -- Setting operation to 'Inserted'

SELECT @Count = COUNT(*) FROM DELETED

if @Count > 0

BEGIN

SET @operation = 'Deleted' -- Set Operation to 'Deleted'

SELECT @Count = COUNT(*) FROM INSERTED

IF @Count > 0

SET @operation = 'Updated' -- Set Operation to 'Updated'

END

if @operation = 'Deleted'

BEGIN

Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)

SELECT GETDATE(),'SQL','Deleted','Applications',Id,NULL from deleted

END

ELSE

BEGIN

SELECT GETDATE(),'Inserted','SQL','Inserted','Applications',Id,NULL from inserted

if @operation = 'Inserted'

BEGIN

Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)

SELECT GETDATE(),'SQL','Inserted','Applications',Id,NULL from inserted

END

ELSE

BEGIN

Insert into Logs(OperationTime,WhichOperation,OperationType,OperationTable,OperationID,WebUserID)

SELECT GETDATE(),'SQL','Updated','Applications',Id,NULL from inserted

END

SQL IDENTİTY RESEED

dbCC CHECKIDENT ('[Applications]', RESEED, 1000000);

1	dbCC CHECKIDENT ('[Applications]', RESEED, 1000000);

Sql Server’da Identity Column ID Reset

DBCC CHECKIDENT ('[UserProfile]', RESEED, 0);
GO

1 2	DBCC CHECKIDENT ('[UserProfile]', RESEED, 0); GO

Database’de Bulunan Tüm Tabloları Silmek

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR

SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)

BEGIN

Exec SP_EXECUTESQL @Sql

FETCH NEXT FROM @Cursor INTO @Sql

END

CLOSE @Cursor DEALLOCATE @Cursor

EXEC sp_MSForEachTable 'DROP TABLE ?'