catalog

This Catalog is the index for different blog posts in structured order

Azure
. Azure.Pricing: https://vijredblog.wordpress.com/2016/09/15/azure-database-pricing-tier-selection/

. Azure.Powershell :
Frequently used Azure SQL Powershell Commands: https://vijredblog.wordpress.com/2017/07/27/frequently-used-azure-powershell-commands/
Point in Time Restore on Deleted DB: https://vijredblog.wordpress.com/2018/05/23/how-to-point-in-time-restore-azure-sql-database-after-deleting-the-database/

. Azure.SQL
. AZURE.SQL.Frequently User SQL Commands : https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/

 

Best Practices: https://vijredblog.wordpress.com/2018/06/22/best-practices-being-devops-in-it/

 

Commandline
Commandline.Groupmembers:  https://vijredblog.wordpress.com/2018/07/10/how-to-operate-multiple-objects-in-ssms-f7/

 

SQLServer

. SQLServer.FrequentlyUsedCommands: https://vijredblog.wordpress.com/2018/06/22/frequently-used-mssql-commands/

. SQLServer.Patch
SQL Server Patches: https://vijredblog.wordpress.com/2018/06/18/sql-latest-available-patch-reference/

. SQLServer.SSMS 
Object Explorer Details: https://vijredblog.wordpress.com/2018/07/10/how-to-operate-multiple-objects-in-ssms-f7/

 

Advertisements
Posted in Azure, Powershell, SQL, Uncategorized | Tagged , | Leave a comment

What are default Index Maintenance settings in Ola hallengren maintenance solution

Summary:

When Fragmentation < 5%, No maintenance
When Fragmentation >= 5% and Fragmentation < 30 %; index ReOrganize
When Fragmentation >= 30%; index Rebuild (If Supported online or Offline)

Note: This is the default and regular scenario. check reference for detailed information.

Ref: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Posted in SQL, SQL Server, Uncategorized | Tagged , , , , | Leave a comment

Validate and delete duplicate records from MSSQL table

I had a situation where Index creation failed and business wants to delete duplicate entries immediately from a specific table. I had to validate the columns which are unique and which had different values. Following is easy script to validate duplicates and delete them.

I had a situation where Index creation failed and business wants to delete duplicate entries immediately from a specific table. I had to validate the columns which are unique and which had different values. Following is easy script to validate duplicates and delete them.

Following SQL can be used to delete duplicate records.

WITH CTE AS(   SELECT *,
       RN = ROW_NUMBER() OVER(PARTITION BY col1, col2, col3 ORDER BY uniqueKey DESC)
   FROM MyTABLE)
DELETE TOP(10000) FROM CTE WHERE RN > 1
-- .

Steps used to validate the data before deletion:
Validate number of duplicate record combinations

;WITH CTE AS (
select  col1, col2, col3, count(*) as mycount
,min(uniqueKey) as min_uniqueuekey, max(uniqueKey) as max_ uniqueKey
from MyTABLE WITH (NOLOCK)
group by col1, col2, col3
having count(*) > 1
)
select count(*) from CTE 
-- Above query indicates duplicate occurrences but not actual number of duplicate records  
-- NOTE: We plan to identify duplicate records based on 3 columns but not all columns of the table. 


-- Verify if the records are unique (pick 2 samples in each combination and check if all other columns are same or not)
;WITH CTE AS (
select  col1, col2, col3, count(*) as mycount
,min(uniqueKey) as min_uniqueKey, max(uniqueKey) as max_uniqueKey
from MyTABLE
group by col1, col2, col3
having count(*) > 1
)
select * from CTE 
join MyTABLE s1 on s1.uniqueKey = CTE.min_uniqueKey
JOIN MyTABLE s2 on s2.uniqueKey = CTE.max_uniqueKey
OR s1.Column	  s2.Column
OR s1.Column3	  s2.Column3
OR s1.Column4	  s2.Column4
OR s1.Column5	  s2.Column5
OR s1.Column6	  s2.Column6
OR s1.Column7	  s2.Column7
OR s1.Column8	  s2.Column8
OR s1.Column9	  s2.Column9
OR s1.Column10	  s2.Column10
OR s1.Column11	  s2.Column11
OR s1.Column12	  s2.Column12
OR s1.Column13	  s2.Column13
OR s1.Column14	  s2.Column14
OR s1.Column15	  s2.Column15
-- OR s1.ModifiedOn	  s2.ModifiedOn (We know this value will be different, exclude from validation) 
-- If all other columns are uniqueue, above query should return 0 rows  


-- Check  maximum number of duplicate records for specific criteria
;WITH CTE AS (
select  [UserID] , [StartDateTime] , [EndDateTime], count(*) as mycount
,min(ScheduleID) as min_scheduleID, max(ScheduleID) as max_scheduleID
from Schedule WITH (NOLOCK) 
group by [UserID] , [StartDateTime] , [EndDateTime]
having count(*) > 1 )
select TOP 10 * from CTE ORDER BY 4 DESC 



-- Delete duplicate records: in multiple iterations:

WITH CTE AS(
   SELECT *,
       RN = ROW_NUMBER() OVER(PARTITION BY col1, col2, col3 ORDER BY uniqueKey DESC)
   FROM MyTABLE )
DELETE TOP(10000) FROM CTE WHERE RN > 1
-- .

Ref: https://vijredblog.wordpress.com/2014/08/21/how-to-identify-and-delete-duplicate-records-in-a-table-sql/

Posted in SQL, SQL Query, Uncategorized | Tagged , , , | Leave a comment

Address High CPU on SQL Server

Following are the guidelines to troubleshoot High CPU scenario in MSSQL Server and fix the problem.

1. Identify running SQL statements taking more CPU
2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and creation and last execution time
3. Find the SQL Stagement, check if this is a procedure
3.A: If this is SQL Procedure, try to recompile the procedure and see if this can fix the problem:
4. Remove Plan Cache for specific Plan
5 A. Remove Plan Cache for Database
5. Remove Cache for entire server/Pool
6. Find Query Execution Plan and identify if any indexes can be added:


-- 1. Identify running SQL statements taking more CPU

-- Quick capture of session, host, login, sql text, blocking text
-- reads, writes, cpu, and writes in tempdb
SELECT  [r].[session_id],
        [s].[host_name],
        [s].[login_name],
        [r].[start_time],
        [r].[sql_handle],
		[st].[text],
        [r].[wait_type],
        [r].[blocking_session_id],
        [r].[reads],
        [r].[writes],
        [r].[cpu_time],
        [t].[user_objects_alloc_page_count],
        [t].[internal_objects_alloc_page_count],
		[r].database_id
FROM    [sys].[dm_exec_requests] AS [r]
JOIN    [sys].[dm_exec_sessions] AS [s]
        ON [s].[session_id] = [r].[session_id]
JOIN    [sys].[dm_db_task_space_usage] AS [t]
        ON [s].[session_id] = [t].[session_id] AND
           [r].[request_id] = [t].[request_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[sql_handle]) AS [st]
WHERE   [r].[status] IN ('running', 'runnable', 'suspended')
--AND r.database_id = 23
GO



-- 2. Find sql_handle, using SQL handle find average execution time in ms, Number of executions, and creation and last execution time
select top 100 ((total_elapsed_time/execution_count)/1000) as AverageExectime_milliSecondas ,plan_handle, creation_time, last_execution_time, execution_count, qt.text, * 
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
   WHERE sql_handle =  0x030094859475487654857485......



-- 3. Find the SQL Stagement, check if this is a procedure 
-- 3.A: If this is SQL Procedure, try to recompile the procedure and see if this can fix the problem: 
sp_recompile [clk.OutboundQueue_ListItemsByItemTypeIdItemId]


-- 4. Remove Plan Cache for specific Plan (Ref: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017)
DBCC FREEPROCCACHE (0x050985493434340304000....)

-- 5.A. Remove Cache for database (Ref: https://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/)
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = 'AdventureWorks');
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);


-- 5.B. Remove Cache for entire server/Pool (Ref: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-2017)
DBCC FREEPROCCACHE 


-- 6. Find Query Execution Plan and identify if any indexes can be added:
SELECT  query_plan
FROM    sys.dm_exec_query_plan(0x050098759845400000......);
GO


--
Posted in Execution Plan, Performance, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Fastest way to fix MSSQL Database with Suspect mode

I had a situation where multiple databases (50+ Databases) changed to Suspect mode.
Simple solution: (assume the DBs are in Server-A, Availability group and Server-B is configured as secondary server)
* Fail over the availability group to Server-B
* Resume Data Movement in Server-A
* Fail back the availability group to Server-A

Other references to fix the problem:
https://www.stellarinfo.com/blog/recover-sql-database-from-suspect-mode/
https://dba.stackexchange.com/questions/175844/how-to-repair-suspect-db

Note: Used Object Explorer Details (F7) option to resume data movement on multiple databases: https://vijredblog.wordpress.com/2018/07/10/how-to-operate-multiple-objects-in-ssms-f7/

Posted in SQL, SQL Error, SQL Server, Uncategorized | Tagged , | Leave a comment

Design considerations for SQL Server performance

While designing SQL Server for high performance following are the options to consider:

1. Block size to 64 KB (Defauly is 4 KB)
Ref:
* https://community.toadworld.com/platforms/sql-server/b/weblog/archive/2016/09/14/sql-server-storage-reading-block-size
* https://blog.purestorage.com/what-is-sql-servers-io-block-size/

2. Default Auto-Growth: change it to 1 GB

3. Validate MAXDOP Settings (MAXDOP and Cost Threshold)

Posted in Performance, SQL, Uncategorized | Tagged , , | Leave a comment

How to operate multiple objects in SSMS (F7)

While working on a service restoration, we had to resume data movement on 100+ random databases. Generating TSQL is the best option but UI will be very quick and reliable.

Biggest challenge with SSMS UI will be to perform the action for each DB one at a time. I learnt about Object Explorer Details (F7) from my college (Thanks John)!

Simple Demo to Script 4 different tables at the same time!

1. Connect to the Server in SQL Server Management Studio (SSMS)
2. Click F7 (You can also click on View tab + Object Explorer Details)
ObjectExplorerDetails1

3. Select Tables from Object Explorer tab. Hold Control and select required table.
4. Right click -> Script Table As -> Create To -> New Query Editor Window
ObjectExplorerDetails2.jpg

Create Table script is generated for all selected tables!

Posted in SQL, SQL Server, SSMS, Uncategorized | Tagged , , | 2 Comments