QTip: Maintenance plan for performance issues demo
1-Requirements 2-Considerations 3-Update statistics 4-Rebuild indexes 5-Missing indexes 6-More information Requirements Azure SQL Database SQL Server Management Studio (SSMS) Considerations Is not recommended to execute in business hours In update statistics and rebuild indexes there is not status of the process it means you will not see % of the process you will see an update when process is finished. Is important to consider number of lines to execute according to size of the tables because SQL is going to execute everything at the same time and may impact performance of the database by instance you can select 5 lines for small tables but 1 line in huge tables 1-Update statistics with fullscan (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Update ALL Statistics WITH FULLSCAN -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will update all the statistics on all the tables in your database. SET NOCOUNT ON GO DECLARE updatestats CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN updatestats DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM updatestats INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'UPDATE STATISTICS ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN' PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below. -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM updatestats INTO @tableSchema, @tableName END CLOSE updatestats DEALLOCATE updatestats GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 2-Rebuild indexes (mandatory, apply to all tables) *When you run script below nothing is going to be executed in line 21 is possible to see that exec is commented script will only create text with commands to execute -- Rebuild indexes -- ONLY GENERATES COMMANDS DOES NOT EXECUTE -- This will rebuild all the indexes on all the tables in your database. SET NOCOUNT ON GO DECLARE rebuildindexes CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE' OPEN rebuildindexes DECLARE @tableSchema NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName WHILE (@@FETCH_STATUS = 0) BEGIN SET @Statement = 'ALTER INDEX ALL ON ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD' PRINT @Statement -- Please do not remove comment in next line unless that you are really sure that you want to run all commands but will cause extra workload to your server --EXEC sp_executesql @Statement FETCH NEXT FROM rebuildindexes INTO @tableSchema, @tableName END CLOSE rebuildindexes DEALLOCATE rebuildindexes GO SET NOCOUNT OFF GO -- end of script After execution you will see results Now you can select number of rows and copy in a new query window 3- Missing indexes (optional review with your development team) SELECT CONVERT (varchar, getdate(), 126) AS runtime, migs.avg_user_impact, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement, 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.avg_total_user_cost,migs.user_seeks,migs.user_scans, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC Based on improvement or avg_user_impact you may decide indexes to create keeping in mind that needs to be analyzed remember adding index increase work with Insert / update / delete operations Copy create_index text of desired commands and copy in a new window to execute Now you are ready to start troubleshooting good luck! More Information How to maintain Azure SQL Indexes and Statistics https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787 AUTO_CREATE_STATISTICS Option https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#AutoUpdateStats Statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16 Query execution times are slow https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#query-execution-times-are-slow Statistics options https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16&redirectedfrom=MSDN#statistics-options When to update statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#when-to-update-statistics Index maintenance strategy https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#index-maintenance-strategy Reorganize an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#reorganize-an-index Rebuild an index https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#rebuild-an-index SQL Server In depth: What can Cause Index to be Still Fragmented After Rebuild https://learn.microsoft.com/en-us/archive/technet-wiki/28182.sql-server-in-depth-what-can-cause-index-to-be-still-fragmented-after-rebuildTiny But Mighty: Unleashing the Power of Small Language Models 🚀
While Large Language Models (LLMs) like GPT-4 dominate headlines with their extensive capabilities, they often come at the cost of high computational requirements and complexity. For developers and organizations looking to implement AI solutions on edge devices or with limited resources, Small Language Models (SLMs) are emerging as a practical alternative. SLMs are not just "smaller" versions of their larger counterparts—they're designed to be faster, more efficient, and adaptable for specific tasks. With fewer parameters and lower computational needs, SLMs open the door to deploying AI on mobile devices, IoT systems, and edge environments without compromising performance. What You Stand to Learn 🧠Introduction to Microsoft's AI Ecosystem Discover Microsoft's end-to-end AI development tools, from Azure AI Services to ONNX Runtime, enabling efficient and secure deployment of AI models across cloud and edge environments. The Advantages of SLMs over LLMs SLMs are game-changers for edge AI applications, providing faster training and inference times, reduced energy costs, and scalability across diverse devices. Hands-On with Phi-3 and ONNX Runtime Experience live demonstrations of SLMs in action with tools like Phi-3 and ONNX Runtime, showcasing how to fine-tune and deploy models on mobile devices, IoT, and hybrid cloud environments. Responsible AI Practices Understand how to safeguard your AI applications with Microsoft's Responsible AI toolkit, ensuring ethical and trustworthy deployments. Watch the Full Session 👨‍💻 📅 Date: December 12, 2024 ⏰ Time: 4 PM GMT | 5 PM CEST | 8 AM PT | 11 AM ET | 7 PM EAT A session packed with live demos, practical examples, and Q&A opportunities. Register NOW | Events | Microsoft Reactor Agenda 🔍 Introduction (5 min) A brief overview of the session and its focus on SLMs and LLMs. Microsoft AI Tooling (5 min) Explore the latest tools like Azure AI Services, Azure Machine Learning, and Responsible AI Tooling. How to Choose the Right Model (10 min) Key considerations such as performance, customizability, and ethical implications. Comparing SLMs vs LLMs (10 min) The strengths, weaknesses, and best use cases for both Small and Large Language Models. Deploying Models at the Edge (10 min) Insights into optimizing AI for mobile, IoT, and edge devices. Q&A Addressing participant questions about AI development and deployment.
Announcing Windows Container on Azure Kubernetes Service Demos
Today I am very happy to announce the release of Windows Container on Azure Kubernetes Service Demos. This Demos repo is a collection of demos to show how you can modernize Windows Server applications with Windows containers running on Azure Kubernetes Service (AKS). GitHub Repo: https://github.com/microsoft/windows-containers-demos
