SSMS Options – Part 2

In SSMS Options – Part 1, I went over several options, including the color theme, track changes, line number and how to get to the options screen.  This post is a continuation of that post.  We will cover a few more options that could be helpful to you when using SQL Server Management Studio.

This post will cover these options

  • Select\Edit
  • Surround object names with brackets
  • Automatically populate Find What with text from the Editor
  • Show Errors


If you right click on a table in the Object Explorer, you will see a number of options, as seen below.  I would like to show how to use Options to change two of these, Select Top 1000 Rows and Edit Top 200 Rows.

These two options do exactly as they say, Select and return rows that you can edit.  Although, these options are here, I will still prefer and recommend that you write the DML statements needed instead.

When you use the “Select” option you will see something similar to what is below.  SSMS writes the DML statement for you and includes a comment at the top.  The results will be returned to you in a grid format.  However, it will return only the number of rows indicated.  In this case, 1000 rows.  You cannot edit these rows in SSMS without writing the proper DML statement.

However, if you click the “Edit….” menu item you can edit the data in the grid by clicking in the cell you would like to edit.  Although, as I stated before, I would recommend that you use the proper TSQL DML statement to complete data modifications.  When you do this, you will see the grid below.  You can now begin editing data.

When you do start editing you will see a small pencil just to the left of the first column.  Once you move to another record or close the table, the record will be saved.

Again, I would recommend that you use the proper DML statements to modify your data.  This looks like it might be a nice short cut…but I wouldn’t do it because the modification can fail.

The question you may have is, how do I change these values?  You will find the answer below.  Change these to what you feel is appropriate.  Notice that both state the TOP keyword, yet we cannot specify the column to do the order by.


Surround object names with brackets……

As you may know, when you drag an object name or a column name from the Object Explorer to the query window, SSMS will drop the name.  This setting will determine is SSMS place brackets around the name or not.


This option can be found, just above the two settings talked about earlier.  The values can either be True or False.

As expected, the results are below.

Automatically populate Find What with text from the Editor

This setting actually can make our lives just a little bit easier.  When this is turned on, SSMS will populate the Find What box with what is selected in the text editor.  You can find this setting in the location below.

If you click “Find and Replace” under the Edit menu, the box highlighted below will appear.  In the box will be what every you have selected in the text, as seen below.

Show Errors

This options can make it easier to locate the errors in your code.  First of all, if can be found under “Text Editor”.

When this is enabled, you will see a red indicator near the right scroll bar when there is an error.  Notice that it is actually on the line below the line that actually contains the error.

That is if there is one error.  Well, what happens if there are two or more errors. The statement below contains two errors, if you notice, the error indicator is on the line below the first error.

When the Show Errors option is turned off, you will not see it, just like in the image below.

Thanks for stopping by my blog!

Optimize for Ad Hoc Workloads

Optimize for Ad Hoc Workloads is one of those server level settings that are not changed very often, but is still good to know about. Before we get into the details let’s talk about what it is. When using SQL Server, it reserves a portion of memory for Plan Cache. The Optimize for Ad Hoc Workloads setting controls what SQL Server places into this plan cache for single use queries. When it is turned off, all single use queries will have the entire plan cached, therefore consuming more space. By turning this on, you are asking SQL Server to not store the entire plan when the query is executed the first time, SQL Server will now only store a plan stub instead. Which consumes less memory than the full plan would. Something to keep in mind, the next time that the query is executed, it will flush the stub from the cache and replace it with the full plan.

There are two methods you can use to determine if this settings is on or off. The first being SQL Server Management Studio(SSMS). In the Object Explorer right click on the server and then click on Properties.  The default is False, meaning that the entire plan will be placed in cache when a query is compiled and executed.

As with many things in SQL Server, you can also use TSQL to get this information. You will need to query sys.Configurations to get this setting via TSQL.

, value
, description
FROM sys.configurations
WHERE Name = ‘optimize for ad hoc workloads’

Here is the result of the above query.

How to determine if there are a lot of Single use Queries in Cache

I think there are two measures we need to understand when it comes to finding out what is consuming the cache. One is the amount of space being consumed by Ad Hoc queries and the other is the number of queries that are considered to be Ad Hoc queries.

Below is the Microsoft description of the dm_exec_cached_plans DMV.  This DMV can be use to capture the amount of space consumed by Ad Hoc queries.

“Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.”  – Microsoft

The code below uses the dm_exec_cached_plans to find out how much space in cache is being consumed by Ad Hoc queries.

From Pinal Dave:

AdHoc_Plan_MB, Total_Cache_MB,
AdHoc_Plan_MB*100.0 / Total_Cache_MB AS ‘AdHoc %’
WHEN objtype = ‘adhoc’
           THEN CONVERT(BIGINT,size_in_bytes)
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
           SUM(CONVERT(BIGINT,size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans) T

Below you will see the results of the above query.  Note that only 2.8% of the cache is being consumed by Ad Hoc queries.

So now let’s see how many Ad Hoc query plans are currently in cache.  The query below used a few additional funtions, sys.dm_exec_sql_text and  sys.dm_exec_query_plan, both of which accepts the plan handle as an argument value. 

SELECT SUM(c.usecounts)
, c.objtype
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q
GROUP BY c.objtype

The results are below

By looking at these two result sets you can see that although there are 2,542 Ad Hoc query plans in cache, they are consuming a small percentage of the total cache, 2.8%.

So what does this mean?  Well, although there are a lot of Ad Hoc queries, if the server is suffering from pressure in memory or in the plan cache, it is probably not due to the Ad Hoc queries.

How do I get the Query Plan

There is a post at that gives  really good explanation on how to get the query plan.

This code from the above link.  It not only tells us the query text and the plan, but also gives us the number of times that plan was used.

SELECT    cplan.usecounts
                , cplan.objtype
                , qtext.text
                , qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC
Below is the result set from the above query.  With this, I can review the query and the execution plan for the Ad Hoc query.
Clear Cache
Of course restarting the SQL Server service will clear the cache, however there is also a method we can used to clear the cache without restarting.  We can use the code below.
When the cache is cleared, whether by a reboot or using the above command, you should let the server run for a bit to let the cache get reloaded with plans.
Does Case Matter?
When talking about the plan cache, case does matter.  However, they will both result in the same execution plan.
Using the AdventureWorks2014 database, when you run the following query you will see the plan in cache.
FROM [AdventureWorks2014].[Person].[Person]
Where BusinessEntityID = 3
The plan is below in the green box.  If you look at the other queries, you will also see the same query with one small difference, the “w” in Where is lower case.  Even though both queries will use the same Execution Plan.  The reason this happens I because SQL Server creates a hash of the query and if the hash value exists in the cache, it will use that plan.  However, because the “w” is a different case, the hash will also be different.
Let’s See it at Work
This is just a simple demo to show that when Optimize for Ad Hoc Workloads is turned on, the plan does not appear in the plan cache with the first execution, only the second.
First thing is to go to the properties of the server and make sure that the Optimize for Ad Hoc workloads is set to true.  Once that is done flush the  cache either by restarting the service or using DBCC FREEPROCCACHE.
Once that is done, run this code to confirm that there are not any plans in cache for the query were are about to run.
The query we are going to use is in the AdventureWorks2014 database:
SELECT * FROM Production.Product
Here is the code to check if there is already a plan in cache for this query.  Make sure to change the database ID, it is 8 on the system I am using.
SELECT c.usecounts
, c.objtype
, t.text
, q.query_plan
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q
WHERE t.text LIKE ‘%select% *%’ AND q.dbid = 8
ORDER BY c.usecounts DESC
Once you run the above code, you will see something similar to the image below.  Notice that there an entry for our query of the product table.
Now run the product table query.  Once you do you will see something like below.  As you can see not only is there a record for our query, but also a plan as well.  This is because the Optimize for Ad Hoc workloads is turned off.
Now, set Optimize for Ad Hoc workload to true, flush the cache and let’s see what happens.  Sure enough, no plan, just as before.
Run the following query:
SELECT * FROM Production.Product
then the query to get the plans.  When you do, you will see the record for the execution, but no plan.
Run the query for the products table a second time. Now you will see the plan is also in cache.  This is because the Optimize for Ad Hoc Workloads, when set to True only stores a stub in the cache on the first execution of a query, then the entire plan on the second execution.
As mentioned earlier in this post, spaces and case do make a difference when SQL Server queries the plan cache.  Notice below that there are two rows for what appears to be the same query.  However, if you look closer, you will see that the second query actually has an additional space between FROM and the table name.  This results in a different hash, and then results in a different entry in the plan cache.
Given all the information should I turn the on or off?
Well, that is a good question.  In my option, unless you have evidence that there is pressure in the cache, I would leave it at the default setting.  I have never actually had a need to change this setting, but am open to it if there is evidence it would benefit the overall performance of the server.  So let’s take a look at what others say.
“I can’t really give you a situation that I would expect to see where this shouldn’t be enabled honestly. There are theoretical examples out there – maybe a series of queries – lots of them – with reusable plans executed only twice. Or something like that. I answer a lot of questions on DBA.StackExchange – it’s a great site in a great network and the consensus there seems to generally be that it almost always makes sense in this answer. When I do a SQL Server Health assessment? I want to see that enabled.`”
Kendra Little Says this on her blog:

“Arguing about this setting is a lot like arguing about whether or not it’s better to put cream in your coffee: if the coffee is decent, it probably tastes fine either way.

My general preference is to not turn on a setting unless I have a good reason to believe that it will make a positive difference, so I don’t turn this on by default. Instead, I’d rather monitor the number of single use plans in cache, and investigate and act accordingly if that number starts spiking upwards.

But admittedly, that’s being fussy: if I had a flock of 1,000 SQL Servers to manage myself and I knew they were all patched to recent supported versions, I’d probably enable it on them all and I wouldn’t feel a bit bad about it, because I wouldn’t have the bandwidth to do it the very best, artisanal, hand-crafted way.”

Pinal Dave states this on his blog:

“Based on the result you can make your own conclusion and change your settings. I, personally, prefer to turn Optimize for Ad Hoc Workloads settings on when I see AdHoc Percentages between 20-30%. Please note that this value gets reset when you restart SQL Server services. Hence, before you change the settings, make sure that your server is up for a quite a few days.

If you are not sure, what to do, just reach out to me with the result and I will be happy to help you.

One final note, This is just one settings, which contributes to your SQL Server Performance”

I tend to be more in line with what Pinal Dave is saying with the understanding that this is only one setting, however all three make very valid points.  You need to look at the big picture and decide for yourself on what the best settings is for your systems. There are a number of other SQL Server settings that could impact performance and you should review them all before making a change.  You should look at the big picture for performance to determine if the server could benefit by changing this setting.

Thanks for stopping by my blog….

SSMS Options – Change Font Size

This is the first of a series of posts to go over the properties of SSMS that can be changed.  I will post these from time to time, many times after I need to use one of them.

To access the Options, you have to go to the Tools menu in SSMS and then click on Options.

When you do this, you will see the screen below. As you can see, there are a ton of options that you can change.  Some are for the Database Engine while some are for SQL Server Analysis Services.  Some are even for Azure or AlwaysOn. For this post we will talk about how to change the font size.

Starting with changing the font when you write queries.  Under the Environment group go to “Fonts and Colors” then simply change the font size.  When you make this change, it takes effect immediately.  No need to restart SSMS. As you can see, you can also change the color and the background color.

Although the focus of this post is the Text Editor, you can also use this to change the font in many different aspects of SSMS.

When changing settings, you must be aware that some settings will require that you restart SSMS. For example, changing the Grid Results will require a restart.  You will see the message below.

As I said before, you can change the font size of many aspects of SSMS, including the result set.  Below is how you can change the size of the Grid font. Simply change the “Show Settings for” to “Grid Results”.  Then change the fonts as you see fit.

This is what the results grid looks like with the default font.

Now let’s change the size of the font to 16. As you can see below, the font is larger and easier to read.

Here is an example of what you will see if you change the font for Extended Events.  This is so much easier for me to read.  This is just an example of the far reach of the Options menu in SSMS.

Font Colors

When writing TSQL, there are many font colors you will see.

Keywords are blue, strings are red, object names are black and comments are green.  You can change these if you like.  Below is an example of the default font colors.
If you decide you would like to change the colors of fonts, you can do so by selecting the proper word type, inside green box below,  and then modifying the settings.  This screen also gives you an example of what the text will look like.

I change the font size when I am presenting so attendees in the back of the room can see the code better.   I like this option because you can adjust your SSMS environment that works for you!

Thanks for stopping by my blog!!


XEvent Profiler

XEvent Profiler is not a SQL Server feature, it is a feature of SQL Server Management Studio(SSMS). It was first introduced in SSMS v17.3 and the XEvent Profiler sits on top of Extended Events technology.  Per Microsoft, XEvents “enables quick access to a live streaming view of diagnostics events on the SQL server”.

You can find XEvent Profiler at the bottom of the server tree in SSMS, just under SQL Server Agent. There are two sessions that come with XEvents, Standard and TSQL.


Starting and Stopping the Sessions

When you first Launch the session and the Extended Event sessions does not exist, SQL Server will create the session, however it not be started.  To start it you need to right click on the session in XEvent Profiler and click “Launch Session”.  If the Extended Events session already exists, SQL Server will start the session.

When you are done doing what you need to do, simple follow the same steps and click “Stop Session”.

Standard Session

When you enable this session it creates an Extended Events session named QuickSessionStandard.  This sessions does not have a target defined and has a Maximum Dispatch Latency of 5 seconds.  If you create an Extended Events session the default Maximum Dispatch Latency is 30 seconds, significantly higher than the Standard session. This session collects a lot of performance information.  However, it also collects login and logout information. Below you will find the list of events that are being collected.

One thing you might notice about the above image is that there are number Predicates defined.  These are all the same, they exclude system processes.

When you launch the session, SSMS will allow you to watch live events.  The screenshot below is what you will see when viewing live events.  Among the information collected, you will find Database Name, Batch SQL, Client Application, User account and the query_hash.  This view can be modified to meet your needs. The changes to the view will remain when you close the session.

TSQL Session

The second session available is the TSQL session.  The Extended Event session is named QuickSessionTSQL.  As the name implies, this session collects information about the execution of TSQL statements. The events are below.  You notice a few things about these events, like the QuickSessionStandard session, the TSQL session does not have a designated target.

  1. Many are also in the QuickSessionStandard session including:
    • Existing_connection
    • Login
    • Logout
    • sql_batch_starting
  2. There are fewer events than in the QuickSessionStandard session
  3. The QuickSessionStandard session has the sql_batch_completed while this does not

Like the QuickSessionStandard session, the events in this session also have a few predicates applied. All of which are to exclude system sessions from the live data.

Viewing Session Data

When you launch either of these sessions, SSMS will also launch the screen that will allow you to view live data, see below.  Something to keep in mind, when you close this window, it will stop the session.

Modifying the Sessions

Although you can modify the view, it may not be the best idea to modify the definition of these sessions. If you find a need to modify these sessions, it might be better to either modify another user defined Extended Event session or create a new session.

Both of these sessions are intended to provide a quick way to collect and view some basic performance information about your SQL Server.  For those of you that like to use Profiler, this could be a good alternative, even though it is not as powerful or very flexible.  Since neither of these sessions has a defined target, the data is not stored.  Once you disable the session the data is gone.  If you really need this data for review at a later day, you can export it.  The export menu can be found under the Extended Events Menu item.

Thank for stopping by!!!

Additional Information



Max Degree of Parallelism vs Cost Threshold for Parallelism

The title sounds like something that you would see for a boxing match.  However, in reality it is a misleading title.  The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.

These two settings actually define the how many and the when in regards to parallel execution plans.  The Max Degree of Parallelism(MDop) simply defines the number of processors\cores that SQL Server will use when the optimizer determines parallelism is needed.  The Cost Threshold for Parallelism is cost threshold of when SQL Server will use parallelism.  The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.

The recommended settings for MDop is the number of cores not to exceed 8.  However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.

The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5.  Yes, the default setting is too low.  By changing this, SQL Server will reduce the number of smaller queries that may use parallelism.  Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make.  There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%.  This in no way means you will see the same improvement, just what I have seen.

Neither one of these settings require a reboot of the service when changing.  It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.

Kendra Little has a very nice video that explains this more in depth.

MAXDOP of Confusion (Dear SQL DBA Episode 8)


This snippet of code can be used to query server configuration settings

, value
, description
FROM sys.configurations

Thanks for reading!!!!!