Have you ever run a query and not all of your output is displaying?

CREATE TABLE #test (col1 varchar(100));
INSERT INTO #test VALUES ('1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30');
SELECT * FROM #test;
DROP TABLE #test;

Or text output:

If you guessed there is a setting for this you’re right! Under Tools -> Options.

I’m showing the default values for the number of characters to output. I.e. 256 in text and 65535 in grid. Obviously I reduced this for my demo (down to 30 characters for each, which is the minimum). In general though I recommend increasing them to the maximum which is 2097152 characters in both text and grid output. That way if you want less you can handle that in the query itself. I.e. SELECT LEFT(Col1,30). To make this easy I just put 999999999 and it auto reduces to the maximum.

And the last thing I’ll mention here is that if for some reason you need to change these (or any number of other) settings for a specific query window you can go to the Query menu or right click in the query pane and then go to Query Options.

It turns out there is an easy setting that forces the resolution to match the window size. Even if you end up with really weird window sizes.

There are two ways (that I’ve found) to turn this on. The easiest is under the menu for the window (left click on the icon at the top left of the window). Then select Smart sizing. (There will be a check to the left of it if it’s turned on.)

On the other hand if you want to make a more global change you can add the /span setting to your shortcut for Remote Desktop.

I’ll be honest, I’ve never actually used either of these myself. They used with the Common Criteria for Information Technology Security Evaluation. The BOL entry for C2 Auditing says that it is going to be removed in a future version of SQL and that you should use the Common Criteria Compliance setting instead. Be warned, if you do need to use C2 Auditing make sure that you have plenty of space. It chews it up like crazy and if you run out of space for your audit files SQL will stop.

Common Criteria Compliance doesn’t appear to do any auditing but does change settings on your machine to bring you into compliance with Common Criteria (although there is an additional script you need to run) so again, don’t use this unless you have been specifically told you need it. Microsoft has a PDF with additional information here: Microsoft SQL Server Common Criteria.

I realize I’m not really giving you a lot of information here, but I’ll be honest, these settings are something you need to be aware of in case you need them, not something you should be really playing with unless you actually expect you’ll be working with them in the future.

It works when I run it this way but not when I run it through xp_cmdshell!

It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.

Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.

I’m running xp_cmdshell as a sysadmin.

If you are running xp_cmdshell as a sysadmin then it’s running as the service account that is running SQL Server. The easiest way to tell what that is, is to look at the output for sys.dm_server_services.

SELECT * FROM sys.dm_server_services;

Look at the service_account column on the row for the SQL Server entry.

I’m running xp_cmdshell as anything other than sysadmin.

I’m making the assumption that xp_cmdshell is set up correctly and is working otherwise. In this case you are looking at the proxy account set up for xp_cmdshell. The easiest way to find it is to look at sys.credentials.

SELECT * FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##';

This time you want to look in the credential_identity column. If there isn’t an entry at all then the proxy for xp_cmdshell isn’t set up and it shouldn’t be working for a non-sysadmin.

Other

All of that said, if you are the one running xp_cmdshell the easiest way to see what account your shell is running under is to use whoami.

EXEC xp_cmdshell 'whoami';

And just look at the output column.

You can click on the down arrow to change the percentage, click on the number to type a percentage or even change it using the mouse wheel. All of that said, none of it helps with the Grid Results pane. And for years I’ve never found a way to change the font size there. I’m pretty sure the option didn’t exist and it was just recently added to SSMS or maybe I just hadn’t found it. That said, I found it!. I’m using SSMS 18.2 so it’s at least in that version and up.

In the Tools -> Options window go to the Fonts and Colors tab. Then under Show Settings for: select Grid Results. From there you can change the font to whatever is most comfortable for you.

Yay my poor aching eyes!

• Start by figuring out how to set a database scoped configuration. Assuming you don’t already know of course.
• Review what the various options are. Go into detail on at least 5.
• Try a few out. Again at least 5.
• Take the database configurations you reviewed and see if you can match them up with server level configurations that do the same thing.
• See if you can be even more granular with the effects you are getting.

 
So for example once you’ve figured out the general command, take a look at MAXDOP. This is something you can set an instance level, database level and even at a query level.

For a long time now SSMS has had the option of scripting for a specific version of SQL Server. This lets you script specifically for 2008 R2, 2012, 2016 etc. Just as an example the script for an extended event changed quite a bit over the first version or two. It’s never huge things, but it does help to script to your target server.

Well in SSMS 16 this was what the option looked like:

Notice how you can select any version of SQL from 2005 to 2016.

But as of v17.9 the option has changed (I’m not sure if it changed somewhere between 17 and 17.9, I haven’t checked).

The important thing to notice here is Match script settings to source. Now rather than having to select the scripting version it automatically selects the version of the server you are on. And of course, if you need to generate a script for a different version you have that option too. Working on an upgrade for example, where you need a script for a higher level of SQL, or perhaps you’re halfway through the upgrade process, and your dev environment is a later version than your production environment so you need to script to a lower version.

In a stable environment, you should be changing the instance configurations rarely if ever. That said, it does happen. And for new environments or even when you upgrade your environment you may need to change something. Heck, something as simple as adding a new application to an existing instance may require changes.

So let’s give this a shot. Remember you want to be making these changes in a private sandbox, or your home lab.

Change the following settings (read the rest of the blog before making any changes):

• Minimum and maximum memory
• Maximum degree of parallelism (MAXDOP)
• Minimum threshold for parallelism
• Default fill factor
• Cross database ownership chaining
• Enable CLR
• Remote Admin Connections
• Turn on database mail
• Enable xp_cmdshell

 
Things to think about here:

• Some of these are considered advanced settings.
• Some will require a reboot, some won’t.
• What are the defaults?
• How do I tell what the current settings are?
• What are some of the other options?
• And of course what does each of these do.

 
Now, before you make any change you always want to make a note of what the previous setting was. (Always have a backout plan.) And of course you always want to know exactly what the setting you are changing is going to do. For example the minimum memory setting does not mean that SQL is going to start up with that amount of memory.

And as always, none of this is particularly hard, but just because it’s not hard doesn’t mean that you’ve done it before. There is absolutely nothing wrong if you haven’t. No time like the present right?

Baselines

Why are baselines important? Have you ever heard The server is slow? Is it? Can you be sure? I mean what exactly is slow? It’s such a personal thing. I’ve seen people complain about queries running slower than usual when they were actually, provably, faster. The only way you can tell is if you know how fast things are on average. In fact, it’s handy to have a normal range. I should probably point out that normal does not mean acceptable. It will, however, tell you if things are getting worse. On top of that if you want useable alerts without a ton of false positives then you need a baseline. That way you can tell when a setting is no longer normal.

Anyway, enough about why, here’s your homework. Read this post by the Tiger Team.

SQL Server Performance Baselining Reports Unleashed for Enterprise Monitoring !!!

You could also read this SE question and its responses (there are also a fair number of good links):

Creating SQL Server Performance Baseline Monitoring

Once you’ve done the reading take some time and think about how you can implement baseline reporting in your environment. Actually setting it up is probably a bit much for this post (although I may add something like that in later) but at least start thinking about it.

If you are using SSMS here are some of my settings so you can see what’s available. I don’t necessarily recommend using these, they are what works best for me but if nothing else they will give you an idea of what can be done.

• Storing code in a solution
• Control what connections open when starting SSMS.
• Add keyboard shortcuts for common tasks
• Put object explorer etc on the right side of the screen.
• Other setup options