To view client statistics, you need to turn on the “Include Client Statistics” option. One way to turn these on are to use the “Include Client Statistics” in the Query menu, as shown below:

Alternatively, you can use the “Shift+Alt+S” keyboard shortcut. When you turn on “Include Client Statistics”, the statistic will be displayed each time you execute your TSQL from a query window. Another way to turn on client statistics is to click on the “Include Client Statistics” button in the toolbar as shown below:

To show you some client statistics let’s run the following query while the “Include Client Statistics” option is enabled:

USE AdventureWorks2019;
GO
SELECT * FROM Sales.SalesOrderDetail 
WHERE SalesOrderID > 1000 and SalesOrderDetailID < 2000;

After running this query, a new tab called “Client Statistics” is displayed in the results pane as shown below:

In this tab, you can see a number of different statistics that are grouped into the following categories:

• Query Profile Statistics
• Network Statistics
• Time Statistics

“Query Profiler Statistics” shows some interesting numbers related to the types of commands and rows returned by the query. The “Network Statistics” show you statistics regarding the network packets and the number of bytes sent to a server and returned from a server. This network information can help you identify the amount of network traffic that a query causes when it is executed. The larger the number of bytes identified, the more network bandwidth the query uses. The last grouping, “Time Statistics”, shows information about the time spent in different areas, while processing a query.

If you look at the columns displayed in the screenshot above, you can see the “Trail 1” and “Average” columns. The “Trail 1” column represents the statistics from the first execution of the TSQL code. Since I have only run the TSQL code once, you can see the “Average” shows the same numbers as the “Trail 1” column. The “Average” column is more useful after more than one execution has occurred. So let’s run the test query two more times.

After running it twice, here is what the client statistics tab now shows:

As you can see, we now have the client statistics for two more trials – 2 and 3. These are the statistics for the second and third execution of our TSQL code. Plus, now the “Average” column contains statistics that are averaged across all three trial executions. Additionally, you can see some little green, red, or black arrows displayed that we have pointed to with the big yellow arrows in the screenshot above.

These arrows indicate whether the numbers went up, down, or stayed the same between each subsequent trial execution. The little red arrow indicates that the number went up since the last trial execution. The little green arrow tells us that the number has gone down from the previous trial. Lastly, the little black arrow shows that, between both trials, the number stayed the same. These little arrow indicators help you quickly identify if the stats are trending up, down, or staying the same between trials, without looking at the actual numbers.

Next time you want to know how much network traffic a client query is causing, are looking to identify averages, or trends between different executions of a given query, you might consider turning on client statistics so you can follow the trends and averages over multiple executions of the same query.

Read: Exploring Templates in SQL Server Management Studio.

The post What are Client Statistics in SQL Server Studio? appeared first on CodeGuru.

Read: Exploring Templates in SQL Server Management Studio.

To create a code template, you must first open the “Template Browser”. This is done by either using the Ctrl+Alt+T shortcut or by clicking the “Template Explorer” item under the “Edit” menu item in the SQL Server Management Studio (SSMS) main menu. When doing this, the “Template Browser” dialog menu will display, as shown below:

When you create new templates, you can create them in an existing folder or create a new folder just for your templates. To create a new template folder, right-click on the “SQL Server Templates” item shown in the above screenshot. Then hover your mouse over the “New” item in the context menu and then click on the “Folder” item as shown below:

When you do this, a new folder will be added to the bottom of the template list and you will be given the chance to name the folder anything you would like. For demonstration purposes, I created a folder named “Demo Folder”.

To create a new template in a folder, right-click on the folder in which you’d like to create the new template. For this demo, I will be creating a new template in the newly created folder (“Demo Folder”) that I just created. When I right-click on the “Demo Folder” the same context menu shown above will be displayed. This time instead of picking the “Folder” item, I will pick the “Template” item and then name my new template “MyDemo” as shown below:

To build the TSQL code for the “MyDemo” template, right-click on the item and then select the “Edit” item from the context menu. Upon doing this, a new query window will be displayed called “MyDemo.SQL”. I can now write the code for my demo template in this new window. For the “MyDemo” template, I will be typing the code below into the new query window. This code will show the top 10 worst performing TSQL commands in the procedure cache, based on the number of times it has been executed:

SELECT TOP 10 QP.query_plan AS [Query Plan], 
       ST.text AS [Query Text],
       QS.creation_time AS [Creation Time], 
       QS.last_execution_time AS [Last Execution Time], 
	   QS.execution_count AS [Execution Count], 
	   QS.total_worker_time AS [Total CPU Time],
	   QS.total_logical_reads AS [Logical IO Count]
FROM sys.dm_exec_query_stats AS QS
   CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
ORDER BY [Execution Count] DESC;

At this point, if we didn’t have any parameters that we wanted or needed to support we could close and save the query window. But for our demo, we want to add one parameter to our template. The parameter will be used to adjust the number of rows returned from this query. The parameter we will create allows us to change the “Top 10” text in the code above to the number of rows we identify for the parameter, or, if we don’t want to change the value of the number of rows returned the the template, it will use the default value, which will only returns 10 rows. To add a parameter, we modify the code above to look like this:

SELECT TOP  P.query_plan AS [Query Plan], 
       ST.text AS [Query Text],
       QS.creation_time AS [Creation Time], 
       QS.last_execution_time AS [Last Execution Time], 
	   QS.execution_count AS [Execution Count], 
	   QS.total_worker_time AS [Total CPU Time],
	   QS.total_logical_reads AS [Logical IO Count]
FROM sys.dm_exec_query_stats AS QS
   CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
ORDER BY [Execution Count] DESC;

As you can see, we changed the value “10” in our original code to look like this:

 <How Many Rows To Return,int,10>

The text between the less than “<” and greater than “>” characters denotes the parameter we added. There are three different parts of defining a parameter, each separated with a comma “,”. The first part is the name of the parameter. In our case that parameter is called “How Many Rows To Return”. The second part is the data type. For that, we specified “int”. The last part is the default value.

The last step in creating our template, is to just save the code in our “MyDemo.SQL” query window. We can do that by just closing the window.

That is all there is to creating a custom TSQL code template that contains a parameter. Next time you have some code that you use over and over again, consider creating a template for your commonly used code. By using templates, you can quickly create and modify sample code using parameters.

The post How to Create TSQL Code Templates in SQL Server appeared first on CodeGuru.

Read: How to find the Worst Performing TSQL Code.

Templates have been around since SQL Server 2000. In SQL Server 2005 the team at Microsoft included the Template Explorer with SSMS. You can bring up a list of templates in a “Template Browser” window by using the Ctrl-Alt-T shortcut or using the Template Explore menu option under the “View” item on the SSMS main menu.

When the Template Browser window comes up it will look like the following image:

As you can see, there are templates for creating scripts related to backups, databases, and endpoints to name a few. To see all of the templates, use the scroll on the right of the Template Browser menu.

If you want to use one of these templates to build your TSQL code, just expand the high-level item you are interested in and then click on the template you would like to use. Below is a screenshot of some TSQL code that was generated when I clicked on the “Backup Database” template item:

You may notice that this TSQL code has parameters. Parameters are identified by angle brackets (<…>) in the TSQL code above. You can easily replace these parameters using the parameter menu, which can be brought up using the Ctrl+Alt+M shortcut. When you use that shortcut, the following menu is displayed:

As you can see, the template I used has 2 different parameters; one named “Database_Name” and the other named “Backup_Path”.

In order to replace these parameters with the appropriate values, all you need to do is type over the default value that is displayed under the “Value” column for each of the parameters you want to change. Once all the parameters are changed, just click the “Ok” button. By doing this, the values entered are then used to replace the default parameter text in the query window.

Using templates can help you to quickly write usable TSQL code for a specific purpose. That code could be a simple create index statement or could be some really complicated code. Next time you want to write some TSQL code to perform a task, check and see if there is a template to help you jumpstart your TSQL code writing.

Read: How to create custom TSQL code templates.

The post Exploring Templates in SQL Server Management Studio appeared first on CodeGuru.

When a TSQL statement runs, it gets compiled and, in most cases, the plan is stored in the procedure cache. In addition, when the plan is executed, the database engine also gathers processing statistics related to the execution and stores those stats in the procedure cache as well. By reviewing the stored stats within the execution plan, you can see how many resources are used to run the TSQL code.

The code below displays a few of these processing statistics for the commands stored in procedure cache:

-- Display top 10 based on execution count
SELECT TOP 10 QP.query_plan AS [Query Plan], 
       ST.text AS [Query Text],
       QS.creation_time AS [Creation Time], 
       QS.last_execution_time AS [Last Execution Time], 
	   QS.execution_count AS [Execution Count], 
	   QS.total_worker_time AS [Total CPU Time],
	   QS.total_logical_reads AS [Logical IO Count]
FROM sys.dm_exec_query_stats AS QS
   CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(QS.plan_handle) ST
ORDER BY [Logical IO Count] DESC;

This code orders those stats by the most Logical I/O used by commands. By reviewing the stats displayed by this TSQL code on your instance, you should be able to determine the commands that perform worse than others, based on I/O. By changing the ORDER BY clause, you can focus in on the worst TSQL code from different performance metrics like CPU, elapsed time, and so forth.

You should consider running code similar to this when any new application or major changes have been implemented into production. If you periodically review the performance of the TSQL code that is running on your server, you can keep on top of poor performing TSQL code. The next time you are trying to find poorly performing TSQL statements on your instance, consider looking at the stats in the procedure cache, using the methods covered in this article.

Read: TSQL Code Templates.

The post How to Find the Worst Performing TSQL Code appeared first on CodeGuru.