Questions will be like this:
Q) How to tune SP and wt is the need to Tune SP?
OR Last week my SP was running perfectly and why it is running too slowly?
Ans :
To tune SP we have to follow the various steps .. those are give below
1. Use Schema Name for Objects in SPs
2. Stored procedure should not start with "SP_" letters
3.Use "SP_ExecuteSql" stored procedure instead of "Execute" statement.
4. Try to avoid SQL Server Cursors when it is possible
5. Use "SET NOCOUNT ON" statement in SP.
6. Try to Drop TempTables to clear Temporary memory to work effectively
7. Do Error Handling with " TRY , CATCH" keywords whenever your required
8. We should create an index on any table (permanent or temporary table) if that is usable in more than one statements.
9. Use IFEXISTS (SELECT 1) instead of (SELECT * )
10. Use DBCC Commands with the following statements
DBCC FreeProcCache;
DBCC DropCleanBuffers;
Go
SET Statistics Time ON;
SET Statistics IO ON;
Go
Exec <Your SP> [Args]
11. Drop/Release objects whichever created and used in that procedure bloc
12. Try to avoid don't use many SubQueries instead use Inner Join
13. Use Query Execution Plan to check time taken for query to get executed.
14. Instead of Cursor use TempTable in the Looping.
15. Try to avoid Temporary tables if possible , instead use CTE or TableVariable if no of rows are less (like below 1000 rows)
------------------------------------------------------------------------------------------------------------
Advance Stored Procedure
The main advantage of stored procedure is, to execute T-SQL statements in less time than the similar set of T-SQL statements is executed individually. The reason to take less time is that the query execution plan for the stored procedures is already stored in the "sys.procedures" system defined view.
- Find the most costly statements
- Determine why the statement is costly
- Get an accurate baseline for the procedure
- Optimize
Find the Most Costly Statments
There are two main ways to determine costly queries. One is to execute the procedure including an ActualExecution Plan. The other is to get a time difference of a before and after for each statement. The are also other ways (including IO usage and CPU usage but we won’t cover those here).
I like to start with the execution plan method. Though this is not the most precise method, it is the easiest.. so let’s start there. Here is how to find the slowest query in a procedure using the execution plan.
Determine why the statement is costly
This can be them most difficult task. With the use of the execution plan, we can help deduce the most common issues.
Now that we’ve found the statement, we need to drill down even further to find out what is causing the statement to be slow. This is done by looking at the cost for each operation in the execution plan. Under each operation, you will see a “cost: x%”. You want to find the operation with the highest percentage and focus on that.
When you do find it, many times it will be one of the following operations:
| Interpreting the Execution Plan | ||
|---|---|---|
| Symptom | Cause(s) | example |
| Table scan, index scan,clustered index scan | Missing or improper Indexes, cardinality < 5% |  |
| thick lines (arrows) from one operation to another | Bad Joins, missing filter operation |  |
| RID Lookups, Key Lookups | Data is not at the leaf level, can use include clause for indexes |  |
| Paralellism | Bad join, improper order of operations, maxdop or parallel threshold set too low on server |  |
| Compute Scalar | Joining on differing data types, implicit conversion required |  |
Scans
Scan operations are not bad when the cardinality of the column being searched is less than approx 6%, or when the amount of records being scanned is below around 500 records. Otherwise, if you have a scan that has a high cost associated with it, you will want to find the appropriate indexes to fix this issue.
Large Work Tables / Results
This is the issue above that is depicted by thick arrows. When this happens a lot of times it is a bad order of operations. What that means is that the optimizer did not choose the limit the overall result set by joining the smallest tables first. Instead, it is trying to join and filter from the largest tables, and then join the smaller tables. While this may not be the only scenario this happens in, it does happen often.
To fix this, you may consider breaking the query up and dump results into a temp table prior joining on the rest of the tables. Make sure to index your temp table!
RID Lookups / Key Lookups
These are also not always bad. In fact you cannot get around this issue all the time. Why? Because you can only have one clustered index, and you don’t want to include every column in the table in all your non clustered indexes. However if you run into this issue having a high cost, you will want to consider changing your clustered index, or adding the columns being looked up in the Key Lookup using the INCLUDE statement for indexes.
Parallelism
Parallelism can have a significant impact on queries and your server. What this means is that the amount of data being usurped by your query is very large, so in order to speed it up, SQL Server thinks it would be best to create more SPIDs to handle the operation. When this happens look for your page life expectency to dip low and your disk utilization to go high. Consider employing the same technique as above of breaking up your query. Also look at the order of operations and change the query around. If that doesn’t help, add the query hint OPTION (MAXDOP 1). Like this:
SELECT *
FROM dbo.mytable mt
JOIN dbo.yada y
ON mt.ID = y.ID
OPTION (MAXDOP 1)
FROM dbo.mytable mt
JOIN dbo.yada y
ON mt.ID = y.ID
OPTION (MAXDOP 1)
Compute Scalar
This guy may fool you when it comes to the cost factor, however it can be a big hindrance to performance when this operation is between table joins. The “scalar” part should worry you, because it does perform somewhat of a row-by-row operation. When this is between table joins, this operation is there to do an implicit conversion of a datatype in order to join the columns correctly. Since it cannot join the two tables directly it will often force too many records to be touched in order to be converted. The conversion may then be dumped into a worktable (in the background) then that worktable will be used for joins after the conversion. Just make sure to join like data types.
This guy may fool you when it comes to the cost factor, however it can be a big hindrance to performance when this operation is between table joins. The “scalar” part should worry you, because it does perform somewhat of a row-by-row operation. When this is between table joins, this operation is there to do an implicit conversion of a datatype in order to join the columns correctly. Since it cannot join the two tables directly it will often force too many records to be touched in order to be converted. The conversion may then be dumped into a worktable (in the background) then that worktable will be used for joins after the conversion. Just make sure to join like data types.
Get an Accurate Baseline
Next before you make changes, get an accurate baseline prior to making changes so when you do make changes, you can be sure that they sped things up.
When you execute a procedure, the time it takes to execute is in the lower right-hand corner as shown below.
Run Duration
From the above, we can see the problem with only using the client side timer. It does not show milliseconds, and milliseconds do matter. The other problem that we cannot see above with the client-side timer is that it also includes the round trip time to the server and back to your computer. While this may seem like a more accurate depiction, it makes optimizing harder because you get less consistent results. In my opinion, it’s better to get the server-side timings.
To get the server-side timings, we turn on the time statistics, using set statistics time command.
We enable it using this:
SET STATISTICS TIME ON
Once executed, it stays on for the entire session (or window) until you close the window or turn it off (by replacing ON with OFF). So you only need to execute this one time, then you can remove the statement.
The next step needed to get consistent results is to clear the caches. One cache holds the compiled version of the SQL Procedure (the execution plan), the other cache holds the data pages that are retrieved from disk.
To clear the compiled execution plan, we use:
DBCC FREEPROCCACHE
To clear the data pages, we use:
DBCC DROPCLEANBUFFERS
Optimize
Now optimize and apply the changes discovered above! I know, easier said than done sometimes. Even though we did not cover all situations for query slowness, hopefully this will provide a good launching pad. We will delve further into fixes in a later article.
================================================================================================================================================
EXPLANATION:
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
EXEC <my sproc> [args]
DBCC FREEPROCCACHE
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.
DBCC DROPCLEANBUFFERS
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.
Above shameless stolen from: this blog post
SET STATISTICS TIME ON
Displays the number of milliseconds required to parse, compile, and execute each statement.
SET STATISTICS IO ON
Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.
As for the accuracy of the query, that is something that you as the developer have to take a look at. I don't think there is an automated way to test the accuracy.
Hopefully that will get you started.
============================================================================================ Useful Links=================
No comments:
Post a Comment