Blog

<h2>Understanding SQL Server Windows Permissions</h2>When working as a SQL Server DBA, troubleshooting Windows authentication and permission issues is a common task. One of the most useful tools I rely on is the xp_logininfo extended stored procedure.Instead of manually digging through Active Directory (AD), xp_logininfo allows you to quickly identify what AD groups a user belongs to—and how those memberships translate into SQL Server permissions.<figure id="figure-chh39z74akmph5ht2b"><img id="chh39z74akmph5ht2b" src="https://images.unsplash.com/photo-1687603921109-46401b201195?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHw2fHxzcWwlMjBzZXJ2ZXIlMjB0cm91Ymxlc2hvb3Rpbmd8ZW58MHx8fHwxNzc5NDY4MzE4fDA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" alt="sql server troubleshooting" class="object-cover bg-gray-100 rounded-lg md:rounded-xl lg:rounded-2xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1687603921109-46401b201195?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHw2fHxzcWwlMjBzZXJ2ZXIlMjB0cm91Ymxlc2hvb3Rpbmd8ZW58MHx8fHwxNzc5NDY4MzE4fDA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Why use AD Groups?</h3>Managing access through Active Directory groups is significantly more efficient than handling individual user permissions. In most organizations, users frequently join and leave teams. By assigning access to AD groups instead of individuals:✅ Access management becomes centralized ✅ Onboarding/offboarding is simplified ✅ Risk of permission misconfiguration is reduced<h3>What is xp_logininfo?</h3>xp_logininfo is an extended stored procedure in SQL Server that returns information about Windows users and groups, including their associated SQL Server login privileges.It’s particularly useful for:<ul><li>Auditing Windows logins</li><li>Troubleshooting permission issues</li><li>Understanding group-based access control</li></ul><figure id="figure-vchusxne4qmph5hxkg"><img id="vchusxne4qmph5hxkg" src="https://images.unsplash.com/photo-1741958193874-6ef299f6b053?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwxfHxkYXRhYmFzZSUyMG1hbmFnZW1lbnR8ZW58MHx8fHwxNzc5NDY4MzI0fDA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" alt="database management" class="object-cover bg-gray-100 rounded-lg md:rounded-xl lg:rounded-2xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1741958193874-6ef299f6b053?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHwxfHxkYXRhYmFzZSUyMG1hbmFnZW1lbnR8ZW58MHx8fHwxNzc5NDY4MzI0fDA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Common Use Cases</h3>1. Check Which AD Groups a User Belongs To &nbsp; EXEC xp_logininfo 'DOMAINNAME\USER', 'all';This command returns all permission paths for a user, including the AD groups that grant access to SQL Server.2. List Members of an AD GroupEXEC xp_logininfo 'DOMAINNAME\AD GROUP NAME', 'members';This shows all members within the specified AD group—but only if that group exists as a login in SQL Server.Syntax Overviewxp_logininfo &nbsp; &nbsp; [ [ @acctname = ] 'account_name' ] &nbsp; &nbsp; [ , [ @option = ] 'all' | 'members' ] &nbsp; &nbsp; [ , [ @privilege = ] variable_name OUTPUT ]Parameters<ul><li>@acctname Specifies the Windows user or group (e.g., DOMAIN\user).</li><li>@option<ul><li>'all': Returns all permission paths for a user</li><li>'members': Returns only the members of a group</li></ul></li><li>@privilege (OUTPUT)</li></ul>Returns the privilege level of the account:<ul><li style="list-style-type: none;" data-mce-style="list-style-type: none;"><ul><li>admin</li><li>user</li><li>NULL</li></ul></li></ul><h3>Permissions Required</h3>To execute xp_logininfo, you must have one of the following:<ul><li>Membership in the sysadmin fixed server role</li><li>Membership in the db_owner role (in some contexts)</li></ul><figure id="figure-hs2ya4oh2qmph5i3fz"><img id="hs2ya4oh2qmph5i3fz" src="https://images.unsplash.com/photo-1639503547276-90230c4a4198?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHw0fHxzZXJ2ZXIlMjBzZWN1cml0eXxlbnwwfHx8fDE3Nzk0NjgzMzJ8MA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" alt="server security" class="object-cover bg-gray-100 rounded-lg md:rounded-xl lg:rounded-2xl" style="aspect-ratio: 16/9;" data-mce-src="https://images.unsplash.com/photo-1639503547276-90230c4a4198?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=M3wyNjI5NjF8MHwxfHNlYXJjaHw0fHxzZXJ2ZXIlMjBzZWN1cml0eXxlbnwwfHx8fDE3Nzk0NjgzMzJ8MA&amp;ixlib=rb-4.1.0&amp;q=80&amp;w=1080" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Additional Examples</h3><ul><li>Check Group Information</li></ul>EXEC xp_logininfo 'BUILTIN\Administrators';<ul><li>List Members of a Group </li></ul>EXEC master..xp_logininfo 'DOMAIN\Group', 'members';<ul><li>Show All Permission Paths for a User</li></ul>EXEC xp_logininfo 'DOMAIN\User', 'all';<h3>Important Considerations</h3>Before relying on xp_logininfo, keep the following in mind:🔹 Domain Controller Connectivity Required SQL Server must be able to contact a domain controller to retrieve AD information. 🔹 Limited to AD Global Groups It primarily returns information about global groups in Active Directory. 🔹 Login Must Exist in SQL Server The account or group must already be defined as a login in SQL Server for results to be returned.

Troubleshooting SQL Server Windows Permissions with xp_logininfo

<a title="SQLBrainBox" href="https://www.sqlbrainbox.com/" data-mce-href="https://www.sqlbrainbox.com/">SQL Server Monitoring tool:&nbsp; www.SQLBrainBox.com</a>Overview Creating a linked server from SQL Server to Microsoft Azure Fabric Database or Datawarehouse enables seamless cross-platform querying. The key to making this work is using an ODBC bridge with a service principal account for secure authentication.The ODBC Bridge is Key To create a Linked Server in SQL Server to MS Azure Fabric Database/Datawarehouse workspace, you need to use a service principal and ODBC Driver 17 for SQL Server. The ODBC bridge acts as the translation layer between your on-premises SQL Server and the cloud-based Fabric endpoint.Follow these steps to set up the linked server:Step 1: Get the MS Azure Fabric Datawarehouse Endpoint Navigate to your Azure Fabric workspace and locate the SQL connection endpoint. It will follow this format:&lt;your-fabric-sql-endpoint&gt;.datawarehouse.fabric.microsoft.comStep 2: Create and Register an Azure AD Application (Service Principal) In Microsoft Entra ID (formerly Azure Active Directory), create and register a new application:<ul><li>Display Name: e.g., Fabric-Linked_Servers</li><li>Application (Client ID): Your service principal client ID</li><li>Secret: Generate a client secret (password) for the service principal</li></ul>Step 3: Grant Access in the Fabric Database/Datawarehouse Ensure the service principal has appropriate permissions in your Fabric database or datawarehouse. Grant the necessary roles so the linked server can read data from Fabric.Step 4: Create a 64-bit ODBC System DSN Create a 64-bit ODBC System DSN on your SQL Server host. Note that 32-bit DSNs do not appear to work for this configuration.<ul><li>Driver: ODBC Driver 17 for SQL Server</li><li>Name: Your chosen ODBC DSN name (e.g., FabricDSN)</li><li>Server: &lt;fabric-sql-endpoint&gt;.datawarehouse.fabric.microsoft.com</li><li>Login ID: &lt;service principal client id&gt;@&lt;tenant id&gt;</li><li>Password: The service principal secret</li></ul>Step 5: Create the Linked Server In SQL Server Management Studio (SSMS), create the linked server with these settings:<ul><li>Provider: Microsoft OLE DB Provider for ODBC Drivers</li><li>Data Source: Your ODBC DSN name</li><li>Security: Select "Be made using this security context" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Remote login: &lt;service principal client id&gt;@&lt;tenant id&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; With password: The service principal secret Once configured, you can query your Azure Fabric Database or Datawarehouse directly from SQL Server using four-part naming:</li></ul>SELECT * FROM [LinkedServerName]...[SchemaName].[TableName]<figure id="figure-0exge5iosmm8f2aawg"><img id="0exge5iosmm8f2aawg" src="https://cdn.durable.co/getty/26kjShb8KRTKgZGfNxuVX5aiFdCMf6VUUZTSQJjY3xxwB7WJgO6Ud85Ii7pRwk9J.jpeg" alt="data analytics" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26kjShb8KRTKgZGfNxuVX5aiFdCMf6VUUZTSQJjY3xxwB7WJgO6Ud85Ii7pRwk9J.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure><h2>AI and Machine Learning Integration</h2>Artificial Intelligence (AI) and Machine Learning (ML) are at the forefront of BI advancements. These technologies enable businesses to automate data analysis processes, uncovering patterns and insights that might otherwise remain hidden. AI-driven analytics tools are becoming more user-friendly, allowing even non-technical users to leverage complex data insights.One significant trend is the rise of predictive analytics, where AI models forecast future trends based on historical data. This capability empowers businesses to anticipate market changes and adjust strategies accordingly, staying ahead of the competition.<h2>Data Democratization</h2>Data democratization is the process of making data accessible to all employees within an organization, regardless of their technical expertise. With self-service BI tools, employees at all levels can access and analyze data, fostering a culture of informed decision-making.<figure id="figure-ovgw1ffj4m8f2aawg"><img id="ovgw1ffj4m8f2aawg" src="https://cdn.durable.co/getty/26TpTWDhPcbPI2K0E4ZJyquRhntqA17n2brA69q8oWoiwbzzYHkMKbbZjHm1IEZ6.jpeg" alt="team collaboration" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26TpTWDhPcbPI2K0E4ZJyquRhntqA17n2brA69q8oWoiwbzzYHkMKbbZjHm1IEZ6.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure>This trend is driven by user-friendly dashboards and visualization tools that simplify data interactions. Companies that embrace data democratization often see increased innovation and agility, as employees are empowered to act on insights swiftly.<h2>Cloud-Based BI Solutions</h2>The shift to cloud-based BI solutions continues to gain momentum. Cloud platforms offer scalability, flexibility, and cost-effectiveness, enabling organizations to handle large volumes of data without significant infrastructure investments.Moreover, cloud-based BI solutions facilitate remote access to data, supporting the growing trend of remote work. Teams can collaborate seamlessly across geographies, accessing real-time data from anywhere in the world.<figure id="figure-oiygpio4nm8f2aawg"><img id="oiygpio4nm8f2aawg" src="https://cdn.durable.co/getty/26CSaAneRM7oeXeB6EMBaPMzK1yrirg7Ozsv2YdeC7Pv8i3FlC5PwhTylq7vjNrJ.jpeg" alt="cloud computing" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26CSaAneRM7oeXeB6EMBaPMzK1yrirg7Ozsv2YdeC7Pv8i3FlC5PwhTylq7vjNrJ.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure><h2>Real-Time Data Processing</h2>In today's fast-paced business environment, real-time data processing is becoming increasingly crucial. Organizations require immediate insights to respond to market changes and customer needs promptly. Real-time BI tools provide up-to-the-minute analytics, enabling quick decision-making.Industries such as finance, retail, and manufacturing benefit significantly from real-time data processing, where timely information can lead to competitive advantages and enhanced customer experiences.<h2>The Rise of Augmented Analytics</h2>Augmented analytics represents the next wave in BI evolution, combining AI algorithms with human intelligence to enhance data analysis. This approach automates data preparation and insight generation, allowing analysts to focus more on strategic interpretation and decision-making.<figure id="figure-qt1q0g83gm8f2aawg"><img id="qt1q0g83gm8f2aawg" src="https://cdn.durable.co/getty/26JMzVla6efpWaNCv5Lwd6ljCL4SVsDe0j33H3be4i6GvF5Zf7mKLS0zREsjEBLU.jpeg" alt="augmented analytics" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26JMzVla6efpWaNCv5Lwd6ljCL4SVsDe0j33H3be4i6GvF5Zf7mKLS0zREsjEBLU.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure>Augmented analytics tools are designed to be intuitive and accessible, reducing the need for specialized skills. As a result, businesses can leverage advanced analytics capabilities without extensive technical training.<h2>The Importance of Data Governance</h2>As organizations increasingly rely on data-driven strategies, robust data governance becomes essential. Ensuring data quality, security, and compliance is critical in maintaining trust and delivering accurate insights.Future BI solutions will likely incorporate advanced governance features, enabling organizations to manage their data assets effectively while adhering to regulatory requirements.<h2>Conclusion</h2>The future of Business Intelligence is bright, with innovations such as AI integration, data democratization, and real-time processing paving the way for more informed decision-making. As these trends continue to evolve, businesses that embrace these advancements will be well-positioned to thrive in a competitive landscape.

Azure SQL DBA Tools: Linked Server to MS Azure Fabric Database / Datawarehouse

<a title="SQLBrainBox" href="https://www.sqlbrainbox.com/" data-mce-href="https://www.sqlbrainbox.com/" data-mce-selected="inline-boundary">SQL Server Monitoring tool:&nbsp; www.SQLBrainBox.com﻿</a><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;" data-mce-selected="1"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Overview</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">The HOBT_ID field in SQL Server represents the Heap or B-Tree ID. It uniquely identifies the allocation unit associated with a specific table or index. This field is primarily used for storage management and query optimization by the SQL Server engine.</td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Definition and Purpose</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;"><ul><li>HOBT_ID is a BigInt value that uniquely identifies a Heap or B-Tree structure within a database.</li><li>It is used to track and manage the physical storage of tables and indexes.</li><li>Each table or index has a corresponding HOBT_ID that SQL Server uses internally for performance and storage operations.</li></ul></td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Usage in System Views</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">Several system views expose the HOBT_ID for analysis and troubleshooting:<ol><li>sys.partitions</li></ol><ul><li>Contains one row per partition of a table or index.</li><li>Column: hobt_id – Identifies the Heap or B-Tree for the partition.</li><li>Example Query:</li></ul>SELECT TableName = o.name&nbsp; &nbsp; &nbsp; &nbsp;, IndexName = i.name&nbsp; &nbsp; &nbsp; &nbsp;, SchemaName = SCHEMA_NAME(o.schema_id)&nbsp; FROM sys.partitions p &nbsp; &nbsp; &nbsp;JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID&nbsp; &nbsp; &nbsp; &nbsp;JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND p.index_id = i.index_id&nbsp; WHERE p.hobt_id = 72057594040811520&nbsp;2. sys.allocation_units<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100.004%;" data-mce-style="width: 100.004%;"><ul><li>Maps allocation units to their corresponding storage structures.</li><li>Column: container_id (which is equal to hobt_id for IN_ROW_DATA allocations).</li><li>Example Query:</li></ul>SELECT au.allocation_unit_id &nbsp; &nbsp; &nbsp;, au.type_desc &nbsp; &nbsp; &nbsp;, au.container_id &nbsp; &nbsp; &nbsp;, p.hobt_id FROM sys.allocation_units au &nbsp; &nbsp; &nbsp;JOIN sys.partitions p ON au.container_id = p.hobt_id WHERE p.object_id = OBJECT_ID('YourTableName'); </td></tr></tbody></table>&nbsp;3. sys.dm_db_index_physical_stats<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100.004%;" data-mce-style="width: 100.004%;"><ul><li>Provides physical statistics for indexes and tables.</li><li>Column: hobt_id – Used to join with sys.partitions for further analysis.</li><li>Example Query:</li></ul>SELECT index_id &nbsp; &nbsp; &nbsp;, index_type_desc &nbsp; &nbsp; &nbsp;, partition_number &nbsp; &nbsp; &nbsp;, hobt_id FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'DETAILED');</td></tr></tbody></table><h3>Relationship with Tables and Indexes</h3></td></tr></tbody></table><table style="border-collapse: collapse; width: 100%;" border="1" data-mce-selected="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><ul><li>For Heap Tables (tables without a clustered index), hobt_id maps directly to the table's heap storage structure.</li><li>For Clustered Indexes, hobt_id represents the clustered index's B-Tree structure.</li><li>For Non-Clustered Indexes, each index has its own unique hobt_id corresponding to its B-Tree structure.</li></ul></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Practical Applications</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;"><ul><li>Storage Analysis: Identifying how data is allocated and stored in the database.</li><li>Index Fragmentation Checks: Using sys.dm_db_index_physical_stats in conjunction with hobt_id.</li><li>Performance Tuning: Understanding how indexes and partitions are being utilized.</li><li>Troubleshooting Corruption Issues: Correlating allocation units with table partitions.</li></ul></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Conclusion</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">The HOBT_ID field is an essential part of SQL Server's internal architecture, providing a means to track and manage storage structures efficiently. Understanding its usage can help in performance tuning, storage analysis, and troubleshooting of database objects.</td></tr></tbody></table> <figure id="figure-8hujl1bdmdm85275p9"><img id="8hujl1bdmdm85275p9" src="https://cdn.durable.co/getty/335m4VqvgpQM2MRDHaS71E7FAl8Ho0lc2Qx9m8mxv6ZUcuKt2JFUisJRu19k3ymE.jpeg" alt="sql server optimization" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/335m4VqvgpQM2MRDHaS71E7FAl8Ho0lc2Qx9m8mxv6ZUcuKt2JFUisJRu19k3ymE.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Indexing for Improved Query Performance</h3>One of the most effective ways to optimize your SQL Server is through indexing. Indexes are data structures that improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space. By creating appropriate indexes, you can dramatically reduce query response times and enhance overall performance.When designing indexes, consider using clustered indexes for columns that are frequently searched, as they determine the physical order of data in a table. Additionally, non-clustered indexes can be useful for columns that are often included in search conditions or join operations.<h3>Optimizing Queries for Better Performance</h3>Writing efficient queries is crucial for optimizing SQL Server performance. Poorly written queries can lead to slow response times and increased resource consumption. To optimize your queries, start by analyzing query execution plans to identify bottlenecks and areas for improvement.<figure id="figure-28i6nmkc0im85275p9"><img id="28i6nmkc0im85275p9" src="https://cdn.durable.co/getty/26Sbe6mMoNLcKUvoKkytlmX6xMD6aZ0GsluApvUZGwHXPzdjxKOrOWAfBZEn06Hb.jpeg" alt="query optimization" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26Sbe6mMoNLcKUvoKkytlmX6xMD6aZ0GsluApvUZGwHXPzdjxKOrOWAfBZEn06Hb.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure>Avoid using SELECT * statements as they retrieve all columns, which can be resource-intensive. Instead, specify only the columns you need. Additionally, leverage subqueries and joins wisely to ensure they don't unnecessarily complicate or slow down your operations.<h3>Data Compression Techniques</h3>Data compression can significantly reduce storage requirements and improve query performance by decreasing I/O operations. SQL Server offers several compression options, such as row-level and page-level compression. These techniques can help you save space without sacrificing performance.Implementing compression can lead to substantial cost savings, particularly for businesses dealing with large databases. However, it's important to evaluate the trade-offs between compression efficiency and CPU usage to ensure that performance gains are maximized.<figure id="figure-8nclf6zmj8m85275p9"><img id="8nclf6zmj8m85275p9" src="https://cdn.durable.co/getty/26xGLSggU3In8zxx6LdARwldinsElT1NkSim45GGCXZ2YdpnXq6sZoUJhHtNnAog.jpeg" alt="data compression" class="object-cover bg-gray-100" style="aspect-ratio: 16/9;" data-mce-src="https://cdn.durable.co/getty/26xGLSggU3In8zxx6LdARwldinsElT1NkSim45GGCXZ2YdpnXq6sZoUJhHtNnAog.jpeg" data-mce-style="aspect-ratio: 16/9;"></figure><h3>Regular Database Maintenance</h3>Regular maintenance is essential for keeping your SQL Server running optimally. This includes tasks such as updating statistics, rebuilding fragmented indexes, and backing up data. Keeping your database well-maintained ensures smooth operations and minimizes downtime.Automating these maintenance tasks with SQL Server Agent can help you save time and reduce the risk of human error. Regular monitoring and adjustments based on performance metrics will further enhance your database's efficiency.<h2>Conclusion: Unlocking the Full Potential of SQL Server</h2>By implementing these data optimization techniques, businesses can unlock the full potential of their SQL Server databases. Whether it's through effective indexing, query optimization, data compression, or regular maintenance, each strategy contributes to improved performance and cost efficiency.Continually evaluating and adjusting your optimization strategies will ensure your SQL Server remains a powerful asset in managing your business's data needs effectively. Embrace these techniques and watch your business thrive in the competitive data landscape.

SQL DBA Tools: HOBT_ID Associated ObjectID

<a title="SQLBrainBox" href="https://www.sqlbrainbox.com/" data-mce-href="https://www.sqlbrainbox.com/">SQL Server Monitoring tool:&nbsp; www.SQLBrainBox.com</a>Here's a t-sql query one can use to get a list of all table index recommendations in either SQL Server or Azure SQL.&nbsp; The improvement measure column provides how much of an improvement that specific index will have on your database.&nbsp; The user seeks and last_user_seek column provide details on the amount of and last use scenario of the particular index was supplied.SELECT &nbsp; &nbsp; &nbsp;improvement_measure =&nbsp; migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &nbsp; &nbsp; &nbsp;,&nbsp; create_index_statement = 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ' ON ' + mid.statement &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ' (' + ISNULL (mid.equality_columns,'') &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ISNULL (mid.inequality_columns, '') &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ')' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ISNULL (' INCLUDE (' + mid.included_columns + ')', '')&nbsp; &nbsp; &nbsp;, migs.*, mid.database_id, mid.[object_id]FROM sys.dm_db_missing_index_groups mig &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handleWHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &gt; 10ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

SQL DBA Tools: Index Recommendations

Laptop monitoring a cloud computing server room

<a title="SQLBrainBox" href="https://www.sqlbrainbox.com/" data-mce-href="https://www.sqlbrainbox.com/">SQL Server Monitoring tool:&nbsp; www.SQLBrainBox.com</a>Here's a simple query you can run within the master database on any Azure SQL Server to view all server connections that failed.&nbsp; The details about the connection failure isn't too specific but a DBA can use it to generally find the root cause of a connection failure.<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-style="border-collapse: collapse; width: 100%;" data-mce-selected="1"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;">SELECT database_name &nbsp; &nbsp; &nbsp;, start_time &nbsp; &nbsp; &nbsp;, end_time &nbsp; &nbsp; &nbsp;, event_category &nbsp; &nbsp; &nbsp;, event_type &nbsp; &nbsp; &nbsp;, event_subtype &nbsp; &nbsp; &nbsp;, event_subtype_desc &nbsp; &nbsp; &nbsp;, severity &nbsp; &nbsp; &nbsp;, event_count &nbsp; &nbsp; &nbsp;, description FROM master.sys.event_log WHERE event_type = 'connection_failed'</td></tr></tbody></table>

Azure SQL DBA Tools: Connection Failure Logs

<a title="sqlbrainbox" href="https://www.sqlbrainbox.com/" data-mce-href="https://www.sqlbrainbox.com/" data-mce-selected="inline-boundary">SQL Server Monitoring tool:&nbsp; www.SQLBrainBox.com﻿</a><h3>Azure synapse | Dedicated SQL Pool | Data Warehouse</h3>Here are some of the query tools that can be used to gain better insight into the current activities of your Azure synapse, Dedicated SQL&nbsp; Pool, or Data Warehouse.&nbsp; Whichever terminology you used, it all points to an Analysis Data Warehouse database in Azure.<h3>Insight Query Tools</h3><h3>Sessions</h3>-- Active ConnectionsSELECT * FROM sys.dm_pdw_exec_sessions where status &lt;&gt; 'Closed' and session_id &lt;&gt; session_id();SELECT session_id()-- Kill Connection, remember to use single quotesKILL '[SID...]'<h3>Monitor</h3>-- Monitor active queriesSELECT * FROM sys.dm_pdw_exec_requests WHERE status NOT IN ('Completed', 'Failed', 'Cancelled') ORDER BY submit_time DESC;<h3>Blocking</h3>-- Collect the top blockingSELECT TOP 500 &nbsp; &nbsp; waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; waiting.object_type AS LockRequestType, &nbsp; &nbsp; waiting.object_name AS ObjectLockRequestName, &nbsp; &nbsp; waiting.request_time AS ObjectLockRequestTime, &nbsp; &nbsp; blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; blocking.request_id AS BlockingRequestId FROM sys.dm_pdw_waits waiting INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; ON waiting.object_type = blocking.object_type &nbsp; &nbsp; AND waiting.object_name = blocking.object_name WHERE waiting.state = 'Queued' &nbsp; &nbsp; AND blocking.state = 'Granted' ORDER BY ObjectLockRequestTime ASC;<h3>Longest Running</h3>-- Find top 10 queries longest running queriesSELECT TOP 10 * FROM sys.dm_pdw_exec_requests ORDER BY total_elapsed_time DESC;<h3>Distributed Queries</h3>--Find the distributed query plan steps for a specific query.SELECT * FROM sys.dm_pdw_request_steps&nbsp; WHERE request_id = 'QID####'&nbsp; ORDER BY step_index;-- Retrieve the distribution run times for a specific SQL step. -- Substitute request_id and step_index with the respective values from Steps 1 and 3.SELECT * FROM sys.dm_pdw_sql_requests WHERE request_id = 'QID####' AND step_index = 2;-- Retrieve the SQL Server execution plan for a query executing on a designated SQL pool or control node. -- Replace distribution_id and spid with the values obtained from the preceding query.DBCC PDW_SHOWEXECUTIONPLAN(1, 78);-- Retrieve information about all workers involved in a Data Movement Step. -- Substitute request_id and step_index with the values obtained from Steps 1 and 3.SELECT * FROM sys.dm_pdw_dms_workers&nbsp; WHERE request_id = 'QID####' AND step_index = 2; -- Find the SQL Server estimated plan for a query executing on a designated SQL pool Compute or Control node. -- Replace distribution_id and spid with the respective values obtained from the preceding query.DBCC PDW_SHOWEXECUTIONPLAN(55, 238); -- Find queries-- Replace request_id with value from Step 1.SELECT waits.session_id, &nbsp; &nbsp; &nbsp; &nbsp;waits.request_id, &nbsp; &nbsp; &nbsp; &nbsp;requests.command, &nbsp; &nbsp; &nbsp; &nbsp;requests.status, &nbsp; &nbsp; &nbsp; &nbsp;requests.start_time, &nbsp; &nbsp; &nbsp; &nbsp;waits.type, &nbsp; &nbsp; &nbsp; &nbsp;waits.state, &nbsp; &nbsp; &nbsp; &nbsp;waits.object_type, &nbsp; &nbsp; &nbsp; &nbsp;waits.object_name FROM sys.dm_pdw_waits AS waits JOIN sys.dm_pdw_exec_requests AS requests ON waits.request_id = requests.request_id WHERE waits.request_id = 'QID####' ORDER BY waits.object_name, waits.object_type, waits.state;<h3>TempDB</h3>-- Monitor tempdbSELECT &nbsp; &nbsp; sr.request_id, &nbsp; &nbsp; ssu.session_id, &nbsp; &nbsp; ssu.pdw_node_id, &nbsp; &nbsp; sr.command, &nbsp; &nbsp; sr.total_elapsed_time, &nbsp; &nbsp; exs.login_name AS 'LoginName', &nbsp; &nbsp; DB_NAME(ssu.database_id) AS 'DatabaseName', &nbsp; &nbsp; (es.memory_usage * 8) AS 'MemoryUsage (in KB)', &nbsp; &nbsp; (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)', &nbsp; &nbsp; (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)', &nbsp; &nbsp; (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)', &nbsp; &nbsp; (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)', &nbsp; &nbsp; CASE es.is_user_process &nbsp; &nbsp; &nbsp; &nbsp; WHEN 1 THEN 'User Session' &nbsp; &nbsp; &nbsp; &nbsp; WHEN 0 THEN 'System Session' &nbsp; &nbsp; END AS 'SessionType', &nbsp; &nbsp; es.row_count AS 'RowCount' FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu &nbsp; &nbsp; INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id &nbsp; &nbsp; INNER JOIN sys.dm_pdw_nodes_exec_connections AS er &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id &nbsp; &nbsp; INNER JOIN microsoft.vw_sql_requests AS sr &nbsp; &nbsp; &nbsp; &nbsp; ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id &nbsp; &nbsp; LEFT JOIN sys.dm_pdw_exec_requests exr &nbsp; &nbsp; &nbsp; &nbsp; ON exr.request_id = sr.request_id &nbsp; &nbsp; LEFT JOIN sys.dm_pdw_exec_sessions exs &nbsp; &nbsp; &nbsp; &nbsp; ON exr.session_id = exs.session_id WHERE DB_NAME(ssu.database_id) = 'tempdb' &nbsp; &nbsp; AND es.session_id &lt;&gt; @@SPID &nbsp; &nbsp; AND es.login_name &lt;&gt; 'sa' ORDER BY sr.request_id;<h3>Memory Consumption</h3>-- Memory consumptionSELECT &nbsp; pc1.cntr_value AS Curr_Mem_KB, &nbsp; pc1.cntr_value / 1024.0 AS Curr_Mem_MB, &nbsp; pc1.cntr_value / 1048576.0 AS Curr_Mem_GB, &nbsp; pc2.cntr_value AS Max_Mem_KB, &nbsp; pc2.cntr_value / 1024.0 AS Max_Mem_MB, &nbsp; pc2.cntr_value / 1048576.0 AS Max_Mem_GB, &nbsp; pc1.cntr_value * 100.0 / pc2.cntr_value AS Memory_Utilization_Percentage, &nbsp; pc1.pdw_node_id FROM &nbsp; sys.dm_pdw_nodes_os_performance_counters AS pc1 -- pc1: current memory JOIN &nbsp; sys.dm_pdw_nodes_os_performance_counters AS pc2 -- pc2: total memory allowed for this SQL instance ON &nbsp; pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id WHERE &nbsp; pc1.counter_name = 'Total Server Memory (KB)' &nbsp; AND pc2.counter_name = 'Target Server Memory (KB)'<h3>Transaction Log Size</h3>-- Transaction log sizeSELECT &nbsp; instance_name AS distribution_db, &nbsp; cntr_value / 1048576.0 AS log_file_size_used_GB, &nbsp; pdw_node_id FROM sys.dm_pdw_nodes_os_performance_counters WHERE &nbsp; instance_name LIKE 'Distribution_%' &nbsp; AND counter_name = 'Log File(s) Used Size (KB)'<h3>Miscellaneous</h3>-- Monitor rollbackSELECT &nbsp; &nbsp; SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END) AS 'TransactionCount', &nbsp; &nbsp; t.pdw_node_id, &nbsp; &nbsp; nod.[type] FROM sys.dm_pdw_nodes_tran_database_transactions t JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id GROUP BY t.pdw_node_id, nod.[type] -- To track bytes and filesSELECT &nbsp; &nbsp; r.command, &nbsp; &nbsp; s.request_id, &nbsp; &nbsp; r.status, &nbsp; &nbsp; COUNT(DISTINCT input_name) AS nbr_files, &nbsp; &nbsp; SUM(s.bytes_processed) / 1024 / 1024 / 1024 AS gb_processed FROM &nbsp; &nbsp; sys.dm_pdw_exec_requests r &nbsp; &nbsp; INNER JOIN sys.dm_pdw_dms_external_work s &nbsp; &nbsp; &nbsp; &nbsp; ON r.request_id = s.request_id GROUP BY &nbsp; &nbsp; r.command, &nbsp; &nbsp; s.request_id, &nbsp; &nbsp; r.status ORDER BY &nbsp; &nbsp; nbr_files DESC, &nbsp; &nbsp; gb_processed DESC;--Collect the top blockingSELECT TOP 500 &nbsp; &nbsp; waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; waiting.object_type AS LockRequestType, &nbsp; &nbsp; waiting.object_name AS ObjectLockRequestName, &nbsp; &nbsp; waiting.request_time AS ObjectLockRequestTime, &nbsp; &nbsp; blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; blocking.request_id AS BlockingRequestId FROM sys.dm_pdw_waits waiting INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; ON waiting.object_type = blocking.object_type &nbsp; &nbsp; AND waiting.object_name = blocking.object_name WHERE waiting.state = 'Queued' &nbsp; &nbsp; AND blocking.state = 'Granted' ORDER BY ObjectLockRequestTime ASC;-- To retrieve query text from waiting and blocking queriesSELECT waiting.session_id AS WaitingSessionId, &nbsp; &nbsp; &nbsp; &nbsp;waiting.request_id AS WaitingRequestId, &nbsp; &nbsp; &nbsp; &nbsp;COALESCE(waiting_exec_request.command, waiting_exec_request.command2) AS WaitingExecRequestText, &nbsp; &nbsp; &nbsp; &nbsp;blocking.session_id AS BlockingSessionId, &nbsp; &nbsp; &nbsp; &nbsp;blocking.request_id AS BlockingRequestId, &nbsp; &nbsp; &nbsp; &nbsp;COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS BlockingExecRequestText, &nbsp; &nbsp; &nbsp; &nbsp;waiting.object_name AS Blocking_Object_Name, &nbsp; &nbsp; &nbsp; &nbsp;waiting.object_type AS Blocking_Object_Type, &nbsp; &nbsp; &nbsp; &nbsp;waiting.type AS Lock_Type, &nbsp; &nbsp; &nbsp; &nbsp;waiting.request_time AS Lock_Request_Time, &nbsp; &nbsp; &nbsp; &nbsp;DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS Blocking_Time_sec FROM sys.dm_pdw_waits waiting &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_waits blocking &nbsp; &nbsp; &nbsp; &nbsp;ON waiting.object_type = blocking.object_type &nbsp; &nbsp; &nbsp; &nbsp;AND waiting.object_name = blocking.object_name &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request &nbsp; &nbsp; &nbsp; &nbsp;ON blocking.request_id = blocking_exec_request.request_id &nbsp; &nbsp; &nbsp; &nbsp;INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request &nbsp; &nbsp; &nbsp; &nbsp;ON waiting.request_id = waiting_exec_request.request_id WHERE waiting.state = 'Queued' &nbsp; &nbsp; &nbsp; &nbsp;AND blocking.state = 'Granted' ORDER BY Lock_Request_Time DESC;<table style="border-collapse: collapse; width: 100%;" border="1" data-mce-selected="1" data-mce-style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 100%;" data-mce-style="width: 100%;"><h3>Columnstore Index Optimization</h3></td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">Run the below query to create a view on the SQL Pool. &nbsp;The last column on this view generates a SQL statement that can be used to rebuild your indexes:CREATE VIEW dbo.vColumnstoreDensity AS SELECT &nbsp; &nbsp; &nbsp; &nbsp; GETDATE() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [execution_date] , &nbsp; &nbsp; &nbsp; DB_Name() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [database_name] , &nbsp; &nbsp; &nbsp; s.name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [schema_name] , &nbsp; &nbsp; &nbsp; t.name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [table_name] , &nbsp; &nbsp; &nbsp; MAX(p.partition_number) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [table_partition_count] , &nbsp; &nbsp; &nbsp; SUM(rg.[total_rows]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS [row_count_total] , &nbsp; &nbsp; &nbsp; SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [row_count_per_distribution_MAX] , &nbsp; &nbsp; &nbsp; CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 0 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [INVISIBLE_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 1 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [OPEN_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [OPEN_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 2 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [CLOSED_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [CLOSED_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_count] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows] , &nbsp; &nbsp; &nbsp; SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] &nbsp; ELSE 0 &nbsp; &nbsp;END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_DELETED] , &nbsp; &nbsp; &nbsp; MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_MIN] , &nbsp; &nbsp; &nbsp; MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_MAX] , &nbsp; &nbsp; &nbsp; AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] &nbsp; &nbsp; ELSE NULL END) &nbsp; &nbsp;AS [COMPRESSED_rowgroup_rows_AVG] , &nbsp; &nbsp; &nbsp; 'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS [Rebuild_Index_SQL] FROM &nbsp; &nbsp;sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg JOIN &nbsp; &nbsp;sys.[pdw_nodes_tables] nt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON &nbsp;rg.[object_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= nt.[object_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND rg.[pdw_node_id] &nbsp; &nbsp; &nbsp; &nbsp;= nt.[pdw_node_id] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND rg.[distribution_id] &nbsp; &nbsp;= nt.[distribution_id] JOIN &nbsp; &nbsp;sys.[pdw_permanent_table_mappings] mp &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON &nbsp;nt.[name] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = mp.[physical_name] JOIN &nbsp; &nbsp;sys.[tables] t &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON &nbsp;mp.[object_id] &nbsp;= t.[object_id] JOIN &nbsp; &nbsp;sys.[schemas] s &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON t.[schema_id] &nbsp; &nbsp;= s.[schema_id] JOIN &nbsp; &nbsp;sys.[partitions] p &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON P.object_id &nbsp; &nbsp; &nbsp;= t.object_id GROUP BY &nbsp; &nbsp; &nbsp; &nbsp; s.[name] , &nbsp; &nbsp; &nbsp; t.[name];</td></tr><tr><td style="width: 100%;" data-mce-style="width: 100%;">Now that you have created the view, run this query to identify tables with row groups with less than 100-K rows. You may want to increase the threshold of 100 K if you're looking for more optimal segment quality.SELECT &nbsp; &nbsp;* FROM &nbsp; &nbsp;[dbo].[vColumnstoreDensity] WHERE &nbsp; &nbsp;COMPRESSED_rowgroup_rows_AVG &lt; 100000 &nbsp; &nbsp; &nbsp; &nbsp; OR INVISIBLE_rowgroup_rows_AVG &lt; 100000;Once you have run the query, you can begin to look at the data and analyze your results. This table explains what to look for in your row group analysis.</td></tr></tbody></table>

Blog

Troubleshooting SQL Server Windows Permissions with xp_lo...

Azure SQL DBA Tools: Linked Server to MS Azure Fabric Dat...

SQL DBA Tools: HOBT_ID Associated ObjectID

SQL DBA Tools: Index Recommendations

Azure SQL DBA Tools: Connection Failure Logs

Azure SQL DBA Tools: Manage Azure Synapse Dedicated SQL Pool