Posts

Columnstore indexes in SQL Server

 Columnstore indexes: Columnstore indexes are a type of index in SQL Server designed to improve the performance of analytical queries and data warehousing workloads. Unlike traditional rowstore indexes, which store data in rows, columnstore indexes store data in a columnar format. This design is particularly well-suited for scenarios involving large volumes of data and analytical processing. Key Characteristics of Columnstore Indexes: 1. Columnar Storage: • In a columnstore index, data is stored in columns rather than rows. Each column is stored separately, allowing for more efficient compression and improved query performance for certain types of queries. 2. Compression: • Columnstore indexes use a high degree of compression, reducing the storage requirements for large datasets. This compression is achieved by storing and encoding data at the column level, eliminating redundancy. 3. Batch Processing: • Analytical queries often involve aggregations, filterin...
Post a Comment
Read more

A brief comparison of Microsoft SQL Server versions from SQL Server 2012 to SQL Server 2022

 SQL Server 2012: • Introduced AlwaysOn Availability Groups for high availability. • Enhanced columnstore indexes for better data warehousing performance. • Contained Databases for easier database portability. • Improved security features, including user-defined server roles. SQL Server 2014: • In-Memory OLTP (Hekaton) introduced for improved transaction processing. • Buffer Pool Extensions for SSD integration. • Enhanced backup to Azure for improved disaster recovery. • JSON support introduced. SQL Server 2016: • PolyBase introduced for querying across relational and non-relational databases. • Stretch Database feature for hybrid cloud scenarios. • Always Encrypted for enhanced security. • Dynamic Data Masking for controlling access to sensitive data. SQL Server 2017: • Introduced support for SQL Server on Linux. • Adaptive Query Processing for improved query performance. • Graph Database capabilities. • Automatic dat...
Post a Comment
Read more

Master database corruption in SQL Server

 Dealing with a corrupted master database in SQL Server is a critical situation, as the master database contains the system catalog and configuration information for the entire SQL Server instance. Here’s a general approach to address master database corruption: Important Note: Before proceeding, ensure you have a recent backup of your master database. Restoring the master database can be a complex process, and having a backup is crucial for recovery. Steps to Address Master Database Corruption: 1. Check SQL Server Error Log: • Examine the SQL Server Error Log for any indications of corruption or issues with the master database. 2. Attempt Emergency Mode: • Start SQL Server in single-user mode using the -m option. Open a command prompt and navigate to the SQL Server executable directory. Run the following command: sqlservr.exe -m 2. • Connect to the instance using SQL Server Management Studio (SSMS) and attempt to run DBCC CHECKDB on the master database: DBC...
Post a Comment
Read more

T-SQL Script to check the database backup and restore progress and estimated completion time

  Script 1:  select  d.name as [Database Name], Percent_complete,  dateadd(second,estimated_completion_time/1000,  getdate()) as EstimatedCompletionTime, Getdate() as Current_Time_now,  CAST((datediff(minute, start_time, getdate())) as varchar(50)) +'  ' + 'Minutes' as CMD_Running_From,  CAST((estimated_completion_time/1000/60) as varchar(50)) +'  ' + 'Minutes'  as Will_Complete_in, start_time as CMD_Start_Time,  command As Running_Command from sys.dm_exec_requests req inner join sys.sysdatabases d on d.dbid = req.database_id where  req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG') Script 2: select  d.name, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as EstimatedCompletionTime, Getdate() as now,  datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo, start_time, command  from sys.dm_exec_requests req inner jo...
Post a Comment
Read more

Facing Performance issue at application end, after migrating the backups to Azure recovery service vault

 ISSUE DESCRIPTION Facing Performance issue at application end, after migrating the backups to Azure recovery service vault (RSV) from Azure Blob storage.  SUMMARY Once backup process started, we could see severe IO latency. The increased IO activity could be due to all backups starting at the same time at 12:30 and reading from the drive.  Earlier, when SQL agent-based BACKUP TO URL was running, it would be working with one database backup at a time which might not put high stress on disk, but Azure RSV is designed to take backup of all databases at the same time (For quick backup, to save time) which can invite considerable IO load on the disks. We checked internally and there are no options to change this behavior for Azure RSV. In Azure blob storage when the backups are running, backup operation is performed serial manner that means server will take the single database backup at a time. But when it comes to Azure RSV, the database backup operation is performed par...
Post a Comment
Read more

SQL Server Dynamic management views (DMV's)

  dm_audit_actions dm_audit_class_type_map dm_broker_activated_tasks dm_broker_connections dm_broker_forwarded_messages dm_broker_queue_monitors dm_cdc_errors dm_cdc_log_scan_sessions dm_clr_appdomains dm_clr_loaded_assemblies dm_clr_properties dm_clr_tasks dm_cryptographic_provider_algorithms dm_cryptographic_provider_keys dm_cryptographic_provider_properties dm_cryptographic_provider_sessions dm_database_encryption_keys dm_db_file_space_usage dm_db_index_operational_stats dm_db_index_physical_stats dm_db_index_usage_stats dm_db_mirroring_auto_page_repair dm_db_mirroring_connections dm_db_mirroring_past_actions dm_db_missing_index_columns dm_db_missing_index_details dm_db_missing_index_group_stats dm_db_missing_index_groups dm_db_partition_stats dm_db_persisted_sku_features dm_db_script_level dm_db_session_space_usage dm_db_task_space_usage dm_exec_background_job_queue dm_exec_background_job_queue_stats dm_exec_cached_plan_dependent_objects dm_exec_cached_plans dm_exec_connections...
Post a Comment
Read more

SQL Server DBA scripts for day-to-day tasks

   1. Check Database Size: SELECT name AS 'Database Name', size * 8 / 1024 AS 'Size (MB)' FROM sys.master_files WHERE DB_NAME(database_id) NOT IN ('master', 'tempdb', 'model', 'msdb'); 2. Check Fragmentation: SELECT OBJECT_NAME(ind.OBJECT_ID) AS 'Table', ind.name AS 'Index', indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC; 3. Backup Information: SELECT database_name, backup_start_date, backup_finish_date, compressed_backup_size/1024/1024 AS 'Backup Size (MB)' FROM msdb.dbo.backupset ORDER BY backup_start_date DESC; 4. Check Active Sessions: SELECT session_id, login_time, host_name, program_name FROM sys.dm_exec_session...
Post a Comment
Read more