Lesson Learned #530: Comparing Execution Plans to Expose a Hidden Performance Anti-Pattern
One of the most powerful features of SSMS Copilot is how it lets you compare execution plans and immediately show you performance issues. In this case, I would like to share with you my lesson learned comparing two queries and how they behave very differently inside the engine. We have the following queries, these are using a table _x_y_z_MS_HighCPU that contains 4 millon of rows. The column TextToSearch is a varchar(200) datatype. -- Query 1 SELECT COUNT(*) FROM [MSxyzTest].[_x_y_z_MS_HighCPU] WHERE TextToSearch = N'Value: 9'; -- Query 2 SELECT COUNT(*) FROM [MSxyzTest].[_x_y_z_MS_HighCPU] WHERE TextToSearch = 'Value: 9'; Since the query texts are different, each will have a different query ID in Query Store. By running the following T-SQL, for example, I can identify the query IDs. SELECT qsqt.query_sql_text, qsq.query_id, qsp.plan_id, qsp.query_plan_hash, qsp.last_execution_time FROM sys.query_store_query_text qsqt JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id WHERE qsqt.query_sql_text LIKE '%SELECT COUNT(*)%' -- FROM [[MSxyzTest]].[[_x_y_z_MS_HighCPU]]%' ORDER BY qsp.last_execution_time DESC; Queries 1 and 2 can be compared directly. Using Copilot, I ran the following prompt: Compare the execution plans for the two queries (query id 1 and query id 2 using Query Store. Highlight any differences in operators, estimated vs actual row counts, or implicit conversions. Running the following prompt : CPU Usage: Please, show the top resource-consuming queries in the current database using Query Store data. Include query text, execution count, duration, CPU time, and logical reads. We could see the impact of using an antipattern:Lesson Learned #525: Tracking Command Timeouts in Azure SQL: Beyond Query Store with Extended Events
A few days ago, we were working on a support case where our customer was intermittently experiencing command timeouts. What made the case interesting was that queries which usually completed in under one second suddenly started taking more than 10 seconds to execute. Since the application — developed in Python using the ODBC Driver 18 for SQL Server — had a command timeout set to 5 seconds, the following error was triggered every time the threshold was exceeded: Error executing command, retrying in 5 seconds. Attempt 1 of 3 with new timeout 5. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') The application had built-in retry logic, dynamically increasing the timeout in each of the three retry attempts, to allow time for the query to complete and to log enough data for post-error analysis. Example logs from the retry logic: (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 5 seconds. Attempt 1 of 3 with new timeout 5. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') INFO:root:Connecting to the DB jmjuradotestdb1 - Thread id 39808 - (Attempt 1/3) INFO:root:Connected to the Database in jmjuradotestdb1 - Thread id 39808 - 0.0445 seconds --- (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 9 seconds. Attempt 2 of 3 with new timeout 9. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') INFO:root:Connecting to the DB jmjuradotestdb1 - Thread id 39808 - (Attempt 1/3) INFO:root:Connected to the Database in jmjuradotestdb1 - Thread id 39808 - 0.0532 seconds --- (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 13 seconds. Attempt 3 of 3 with new timeout 13. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') (RunCommandTimeout) - Thread: 39808 - Loop:2/5 Execution Time: 9.7537 seconds My first prompt using SSMS Copilot was this "Review the queries that experienced a command timeout or were aborted in the last 30 minutes. Include query text, queryid, duration, and the reason and code for the abort if available." and I got the following results. So, all points that the query 216 got command timeouts. My next question, was, for query ID 216, show the number of total executions reporting that is 28 executions. The response showed 28 executions, but this number didn’t match the number of aborted and non-aborted executions observed in the application logs, why this difference? Checking the table sys.query_store_runtime_stats I found 10 rows all having execution_type = 3, and total executions 28. So, that's mean that Query Store aggregates query execution data over a fixed interval. So, the execution_type is an indicator that at least an execution during this runtime interval was aborted. So, at least several of them were aborted and other not. To obtain a more granular and accurate picture, I created an Extended Events session to capture these events using ring_buffer target. CREATE EVENT SESSION [CommandAborted] ON DATABASE ADD EVENT sqlserver.attention( ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username, sqlserver.database_name, sqlserver.sql_text ) ) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS); GO ALTER EVENT SESSION [CommandAborted] ON DATABASE STATE = START; after reproducing the command timeout scenario again, I was able to see only the aborted executions. So, in this case, 28 executions were executed and 7 executions were aborted. WITH RingBufferXML AS ( SELECT CAST(t.target_data AS XML) AS target_data FROM sys.dm_xe_database_session_targets t JOIN sys.dm_xe_database_sessions s ON t.event_session_address = s.address WHERE t.target_name = 'ring_buffer' AND s.name = 'CommandAborted' ) SELECT x.value('@name', 'varchar(50)') AS event_name, x.value('@timestamp', 'datetime2') AS event_time, x.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name, x.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text, x.value('(data[@name="duration"]/value)[1]', 'bigint') AS duration_microseconds, CAST(x.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS decimal(10,3)) AS duration_seconds FROM RingBufferXML CROSS APPLY target_data.nodes('//event') AS tab(x) WHERE x.value('@name', 'varchar(50)') = 'attention' and x.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') = 'TEST-DataCon' ORDER BY event_time DESC;Exploring AI Development and Management: A Journey through Contoso Chat and LLM Ops
In this blog, we'll navigate through the world of AI models, exploring Contoso Chat, Prompt Engineering, limitations of Prompt Engineering, and Large Language Models. We'll introduce tools like the RAG Pattern and Azure AI Studio that can boost AI responses and system performance. Ready to dive into the intricacies of AI development and management? Join us!Introduction to Prompt Engineering
With GPT-3, GPT-3.5, and GPT-4 prompt-based models, the user interacts with the model by entering a text prompt, to which the model responds with a text completion. Basic concepts and elements of GPT prompts Prompt components Instructions Primary Content Examples Cue Supporting content Prompts Basics Text prompts are how users interact with GPT models GPT models attempt to produce the next series of words that are most likely to follow from the previous text. Prompts | Best Practices Be Specific: Leave as little to interpretation as possible. Restrict the operational space Be Descriptive: Use analogies Double Down:Sometimes you may need to repeat yourself to the model. Give instructions before and after your primary content, use an instruction and a cue, etc. Order Matters:The order in which you present information to the model may impact the output. Whether you put instructions before your content (“summarize the following…”) or after (“summarize the above…”) can make a difference in output. Even the order of few-shot examples can matter. This is referred to as recency bias. Give the model an “out” :It can sometimes be helpful to give the model an alternative path if it is unable to complete the assigned task. For example, when asking a question over a piece of text you might include something like "respond with ‘not found’ if the answer is not present". This can help the model avoid generating false responses Prompt components Instructions When we show up to the present moment with all of our senses, we invite the world to fill us with joy. The pains of the past are behind us. The future has yet to unfold. But the now is full of beauty simply waiting for our attention. Instructions are likely the most commonly used prompt component Instructions - instruct the model on what to do Space efficiency TABLES As shown in the examples in the previous section, GPT models can understand tabular formatted data quite easily. This can be a space efficient way to include data, rather than preceding every field with name (such as with JSON). WHITE SPACE Consecutive whitespaces are treated as separate tokens which can be an easy way to waste space. Spaces preceding a word, on the other hand, are typically treated as part of the same token as the word. Carefully watch your usage of whitespace and don’t use punctuation when a space alone will do. Advanced techniques in prompt design and prompt engineering Certain models expect a specialized prompt structure For Azure OpenAI GPT models, there are currently two distinct APIs where prompt engineering comes into play: Chat Completion API Completion API Each API requires input data to be formatted differently Use of affordances | Factual claims, Search queries and Snippets Factual claims: John Smith is married to Lucy Smith John and Lucy have five kids John works as a software engineer at Microsoft Search queries: John Smith married to Lucy Smith John Smith number of children John Smith software engineer Microsoft Snippets: [1] … John Smith’s wedding was on September 25, 2012 … [2] … John Smith was accompanied by his wife Lucy to a party [3]John was accompanied to the soccer game by his two daughters and three sons [4] … After spending 10 years at Microsoft, Smith founded his own startup, Tailspin Toys [5] John M is the town smith, and he married Fiona. They have a daughter named Lucy System message framework and template recommendations for Large Language Models (LLMs) Define the model’s profile, capabilities, and limitations for your scenari Define the specific task(s) Define how the model should complete the tasks Define the scope and limitations Define the posture and tone Define the model's output format Define the language and syntax Define any styling or formatting Provide example(s) to demonstrate the intended behavior of the mode Describe difficult use cases Show the potential “inner monologue” Define additional behavioral guardrail Identify and prioritize the harms you’d like to address.How to Get Started with Copilot in Microsoft Dataverse
Discover the power of Microsoft Dataverse and Copilot in this comprehensive guide. Learn to leverage Copilot's capabilities for seamless table creation and modification. Explore UI differences, understand how to upload Excel files to Dataverse effortlessly, and gain insights into auditing company employee records. Unlock the full potential of these tools for streamlined workflows and efficient data management. Dive into our step-by-step tutorial now!GitHub Codespaces and GitHub Copilot: 2 stories, thousands of possibilities
The year 2022 marked a turning point for GitHub and all its users, with the announcement of the general availability of two revolutionary services for developers: GitHub Codespaces and GitHub Copilot. To give you a deeper understanding of what these services are and how you could benefit from them, let me tell you a story, or better two.
'Do you want to make this call' prompt
I have managed to set up click to call through chrome extensions, but I still get the prompt in teams ' Do you want to call this number?' every time I try to ring a number. Is there any way to remove this prompt and just have it call the number when I click it?
