SQL Server performance isn’t a simple checkbox you check that improves database performance. You have to look at many different factors to gain incremental improvements. Each change might give you a 5-10% improvement, but 10 changes might lead to a 50-80% improvement to the speed of your stored procedure.
In this article by Pinal Dave, we see some of the most common tweaks to your stored…
Welcome to Kodblems, a growing reliable online developers, programmers community to learn, share their programming knowledge, and build their careers.
We share blog How to create stored procedure in mysql with example and 5 Career Pathways with an ITIL service transition certification, Mysql Database, Stored Procedure Training.
This week, I had a stored procedure taking 2 to 3 seconds to execute from a .NET application, where it previously took under 40ms. A few seconds doesn't seem that long, but the application was looping through a collection of items and calling the procedure around a hundred times. Meaning for the user, the time to complete the workflow had increased from around 4 seconds to over 4 minutes. Nothing had changed with the tables being used and index fragmentation was not an issue, thanks to fairly aggressive maintenance plans.
What was more baffling was the same process looping the same stored procedure used in the same application installed in a test environment took the expected four seconds or so. In SSMS, in both production and test environments, everything was well-behaved. Firing up the profiler, I could see that the sproc was reading around a thousand records when executed from SSMS (regardless of environment), but when the .NET application ran it, it was reading 250,000.
The culprit was the execution plan. Or rather plans. Plural. SSMS was getting one plan and the .NET application was getting another. To figure out why SQL was using two separate plans, you need to get the handles for the plans for the stored procedure:
SELECT sys.objects.object_id, sys.dm_exec_procedure_stats.plan_handle, [x].query_plan FROM sys.objects INNER JOIN sys.dm_exec_procedure_stats ON sys.objects.object_id = sys.dm_exec_procedure_stats.object_id CROSS APPLY sys.Dm_exec_query_plan(sys.dm_exec_procedure_stats.plan_handle) [x] WHERE sys.objects.object_id = Object_id('StoredProcName')
From there, consulting the SET options for the plans revealed something interesting: one plan had ARITHABORT ON, the other ARITHABORT OFF. SSMS, by default, has it set to on. Client applications have it set to off. The result was that SQL was using a horrifically bad execution plan for the .NET application and a reasonable one for SSMS. This is usually tied to issues with the parameter sniffing that SQL uses when compiling a plan. The solution I decided to go with was to queue the sproc for a plan recompile:
EXEC sp_recompile N'StoredProcName'
The next time the sproc was executed, SQL generated a new plan for it. The result was a drop from 250k reads and 3 seconds per execution to around a thousand reads and under 40ms. Exactly what it should be.
In this lesson, we’re going to learn about the While Loop. So, what is a While Loop? Well, While Loop is set up using a While statement. While statements are used to repeatedly execute a block of SQL statements. That’s where the Loop phrase comes from because we’re kind of going around and around in a loop as we execute the statements.
After watching this video you’ll be able to declare…
In this section we are going to talk about debugging
stored procedures. Before we get into
working through the mechanics of how to debug and start procedure, I thought we
would just talk about the background and what debugging really is.
Way back in the early days when computers were first being
invented, they were mechanical and one of the first issues that they had with a
computer was there…
I recently put together a lesson on why we need SQL stored procedures. It is part of my Stored Procedures Unpacked course. SQL Stored procedures are a great way to encapsulate logic securely, while allowing client program an easy way to execute it.
The good news is that writing SQL stored procedures isn’t as hard as everyone makes them out to be.
After reading this article you will understand what a database cursor is, see an example, and understand when to use them in a stored procedure.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server.
