The SET STATISTICS TIME command is used in order to identify parse, compile, and execution times that occur on the server-side. It is important to note, these times do not include the time it takes to transfer the data to the client. In order to turn on, simple execute the statement: [cc lang=”sql”] SET STATISTICS TIME ON [/cc] Once the statement is executed, it is turned on for the entire session, and will stay on until it is turned off. (In order to turn it off, replace the ON keyword above with OFF). After the statement is turned on and queries are executed, the parse, compile & execution times are displayed in the messages tab next to the results tab of the query results. Here is an example: For the results above, we first see two “SQL Server Parse and Compile times”. For both of them in this example they are 0. The first result, relates to how long it took to compile the actual “EXEC” statement. The second parse and compile relates to how long it took to compile the entire procedure. Since it is 0, this means the stored procedure is using an existing execution plan, meaning it has already been compiled. The next two lines relate to the two statements that exist in the stored procedure. The first took 123 ms, while the next took 210 ms. The last statement is the overall time it took to execute the procedure. Overall, the SET STATISTICS TIME statement is […]
Continue reading ...
There are many factors to speeding up stored procedures. Knowing these in advance can save you and your company many hours of headaches. Using these practices when writing procedures, you can turn your SQL Server into a fine-tuned machine. This tutorial will cover the specifics to help you tune your procedures optimally. Know your tables The biggest way you can help SQL Server speed up your queries, is by helping it determine how to limit the result set as soon as possible. One of the biggest factors in this is in knowing the tables you are joining together. This translates to: How many records are in your tables? What are the best ways to join your tables? What are the existing indexes on the tables? Knowing the number of records in a table is very important. When joining tables using inner joins, you always want to join the smallest table first. Why you ask? Say you do not use a WHERE clause and you simply join three tables together. If you start your FROM clause with the largest table, then the first table SQL sees is a very large table, so it sets up an execution plan for a very large result set. Then let’s say the last of the 3 tables only has 10 records, by the time SQL visits that table it’s too late, the execution plan has been set to output 100,000 records, whereas if you specified the first table with 10 records after the FROM clause, […]
Continue reading ...
If you landed on this article, then you most likely know that temp tables can cause recompilation. This happens because temp tables are treated just like regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. A similar sort of phenomenon also happens in temp tables. With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. This is especially beneficial to stored procedures that contain many statements. In this example, we will create an example showing recompilation, how to detect it, and how to prevent it. First, we’ll create a procedure that selects a parameterized number of rows from a table. We’re going to use a temp table for purposes of this example. [cc lang=”sql”] CREATE PROCEDURE spGetPresident ( @Rows int ) AS BEGIN SET ROWCOUNT @Rows SELECT * INTO #Presidents FROM dbo.Presidents_Tmp SELECT * FROM #Presidents SET ROWCOUNT 0 END [/cc] Next, before we execute the procedure, we will set a trace up to determine if we are recompiling. In management studio, go to tools -> SQL Server Profiler Connect to your server then choose the ‘Events Selection’ Tab. Unselect all the […]
Continue reading ...
SQL Server 2012 actually employs a different method for pagination that is built in using the ORDER BY Clause. However for other versions, the most efficient pagination method is actually simple. Aren’t most things like this? It involves using (the mighty powerful) ROWCOUNT statement — twice. Anyways, the code: [cc lang=”sql”] CREATE PROCEDURE [dbo].[spUsersPaged] ( @RowBegin int, @MaxRows int ) AS BEGIN DECLARE @StartID int SET ROWCOUNT @RowBegin SELECT @StartID = UserID FROM dbo.Users u ORDER BY UserID — Set maximum number of rows to return SET ROWCOUNT @MaxRows SELECT * FROM dbo.Users u WHERE UserID >= @StartID ORDER BY u.UserID SET ROWCOUNT 0 END [/cc] That’s it for the first pagination sample. You may try other methods, however I challenge all Ye to overcome thy double ROWCOUNT method. Comment below please. To give a variety, I’m sharing another pagination method. This one seems more elegant. It uses a Common Table Expression) CTE, and the RowNumber function. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 WITH SQLPaging AS ( SELECT TOP(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc] Yet is it elegant, with speed?? ..uh not so much.
Continue reading ...
The following statement turns snapshot isolation on: [cc lang=”sql”] ALTER DATABASE sqlserverplanet SET ALLOW_SNAPSHOT_ISOLATION ON GO ALTER DATABASE sqlserverplanet SET READ_COMMITTED_SNAPSHOT ON [/cc] Once snapshot isolation is on, rows that have been modified will fill the version store. To find the amount of space the version store is using, execute: [cc lang=”sql”] SELECT version_store_in_kb = version_store_reserved_page_count*8192/1024 FROM sys.dm_db_file_space_usage [/cc]
Continue reading ...
