SQL Operations Studio Dashboards

SQL Operations Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server databases. I have a great experience running this tool on Linux CentOS and you can build server and database management dashboards  like below. To create custom dashboards check out my tip https://www.mssqltips.com/sqlservertip/5434/customize-sql-operations-studio-dashboards/  

What is Stretch Database?

Stretch Database is a feature of SQL Server where data can be split between on-premises storage and cloud storage. With Stretch Database, cold, historical data is kept in the cloud and active data is kept on-premises for maximum performance. Stretch Database requires no changes to client applications or existing Transact-SQL queries, so you can implement … Continue reading What is Stretch Database?

Moving the msdb, model, and tempdb databases files

All system databases, except the resource database, can be moved to new locations to help balance I/O load. To move the msdb, model, and tempdb databases, perform the following steps: For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement. Stop the instance of SQL Server. Move the files to the … Continue reading Moving the msdb, model, and tempdb databases files

Understanding parallelism on SQL Server

Parallelism refers to multiple processors cooperating to execute a single query at the same time. Parallel execution involves the overhead of synchronizing and monitoring the tasks. So, that's why parallel plans are considered expensive operations. The query optimizer determines whether a parallel plan should be used based on the configuration and the query cost. A configuration … Continue reading Understanding parallelism on SQL Server

TempDB summary

  Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create.   In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage … Continue reading TempDB summary

Difference between Lock and Latch

Locks everywhere is a good start to understand how SQL Server provides logical consistency. Every operation has a lock and latch. What does Latch mean? Latch protects memory on Buffer Pool, is a method that provides physical consistency. SQL Server does operations in memory, that means, it read the page from disk and put that … Continue reading Difference between Lock and Latch

SQL Server threads architecture

SQL Server retrieves threads from Windows. The SQL Server configuration setting max worker threads (set at instance level) determines how many threads will be retrieved. SQL Server has its own internal scheduling system, independent of the scheduling performed by the operating system. Instead of using Windows threads directly, SQL Server creates a pool of worker … Continue reading SQL Server threads architecture