As organizations deploy SQL Server on Linux, leveraging native Linux tools for monitoring becomes essential to ensure optimal performance and reliability. Tools like htop, vmstat, and iostat provide valuable insights into system resources and help identify potential bottlenecks. This blog explores how to use these native Linux tools to monitor SQL Server effectively.
Author: Stephen Planck
Archiving Old Transactions in SQL Server Using PowerShell
Archiving old or obsolete data is an effective strategy to maintain optimal performance. In this post, we’ll explore how to archive old transactions from a SQL Server database using PowerShell. We’ll walk through creating the necessary database and tables, populating them with sample data, executing a PowerShell script to automate the archiving process, and verifying the results.
Exploring Programming Constructs in T-SQL – Part 2: Loops and CASE Expressions
Welcome back to our series on programming constructs in T-SQL! In Part 1 of this series, we explored the fundamentals of variables and conditional IF statements, laying the groundwork for dynamic and efficient SQL scripting. In this second installment, we’ll delve deeper into T-SQL by examining two more powerful constructs: Loops (specifically the WHILE loop) and CASE Expressions. Understanding these constructs will enable you to handle repetitive tasks and implement conditional logic directly within your SQL queries, further enhancing the flexibility and capability of your scripts.
Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server
Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It’s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I’ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.
Understanding the Timeline of Query Optimization Improvements in SQL Server 2022
Intelligent Query Processing (IQP) is a suite of advanced features introduced in SQL Server 2017 and enhanced in subsequent versions, including SQL Server 2022. By upgrading to SQL Server 2022 and setting your database compatibility level to 160, you enable capabilities that dynamically optimize query performance. Understanding when these performance improvements take effect can help database administrators and developers plan and manage their optimization strategies effectively. The journey toward maximum performance improvement is gradual and influenced by factors such as workload characteristics and system configurations.
Five More SQL Server Error Log Messages You Should Care About
In a previous post, we explored five significant SQL Server error log messages that demand immediate attention. However, the SQL Server error log contains a wealth of additional information essential for maintaining the health and performance of your databases. In this sequel, we will highlight five more error log messages that should be on your radar. We’ll look into what each error means and offer suggestions for when you encounter them.
Securing NorthPine Bank’s Data: How SQL Server 2022 Can Help
NorthPine Bank, a fictitious yet representative financial institution, recognized the necessity to modernize its data infrastructure to safeguard sensitive customer information against emerging threats. The bank decided to migrate its operations to SQL Server 2022, leveraging its advanced security features to enhance data protection, ensure regulatory compliance, and maintain operational efficiency. This blog explores the specific security challenges faced by NorthPine Bank and details how SQL Server 2022 addresses these issues through its robust, built-in features.
The Hidden Costs of Over-Indexing SQL Server Tables
Over-indexing in SQL Server can lead to hidden costs that undermine database performance, including increased storage consumption, performance overhead on data modifications, and longer maintenance windows. This article explores how excessive indexing impacts query optimization, disk I/O, and system resources. Learn why a balanced indexing strategy is critical and discover best practices for managing your indexes effectively. Avoid the pitfalls of over-indexing and optimize your SQL Server for both performance and cost-efficiency.
Managing SQL Server Database Growth with Files and Filegroups
As SQL Server databases expand over time, managing their growth becomes an important task. If not handled properly, this growth can lead to performance issues and maintenance headaches. One effective way to manage database growth is by using filegroups and data files thoughtfully. With proper planning, you can distribute your data across different storage devices, enhance performance, and keep your database scalable.
Using Query Hints to Solve SQL Server Performance Issues
As professional DBAs, we’re often tasked with balancing query performance against resource consumption. While SQL Server’s query optimizer generally does an excellent job at selecting execution plans, there are times when we need more control. This is where query hints come into play. These hints provide a powerful way to override the default behavior of the query optimizer, allowing us to directly influence how queries are executed to solve performance bottlenecks. In this post, we will explore how query hints can be leveraged to address common performance issues and fine-tune your SQL Server workloads.