Press "Enter" to skip to content

Month: December 2015

Set SQL Server Startup Parameters With Powershell

Published 2015-12-17 by Kevin Feasel

Mike Fal has a function for managing SQL Server startup parameters:

Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.

Thanks to Mike for making that available to the community.

Comments closed

Dynamic Pivoting

Published 2015-12-16 by Kevin Feasel

Michael Bourgon has a pivot script:

Say you have records from your monitor that happens to Track EventLogs.
We’ll call it EventLog_Tracking for argument’s sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.

Dynamic pivoting is possible (I have an example en passant in a tally table presentation I created a couple years back), but it’s not the easiest thing in the world.

Comments closed

Keep .Net Framework Up To Date

Published 2015-12-16 by Kevin Feasel

Allan Hirt with a public service announcement:

Microsoft recently published an official .NET team blog post reiterating that .NET Framework versions 4, 4.5, and 4.5.1 will no longer receive security updates, support, or hotfixes as of January 12, 2016. This was first announced back in August of 2014, so it’s not like this is new news, but I can say from experience virtually no one is talking about it. MS’ new post talks more about the upgrade path. To sum it up, you need to install .NET Framework 4.5.2, 4.6, or 4.6.1 to be considered supported when it comes to your .NET Framework version. Security is a real issue for many, and those responsible may not know that your version of .NET Framework could be a possible attack vector. Is your security team aware of this impending problem? How will this affect your version matrices (you do have those, right?)?

This is a cross-cutting concern, and I know a majority of database administrators aren’t directly responsible for .Net Framework patches, but work with whoever is responsible and keep them up to date.

Comments closed

Mass Table Renaming

Published 2015-12-16 by Kevin Feasel

Monica Rathbun needed to rename a table in a lot of scripts.  Here’s how she did it:

The quick answer that I came up with is to script out all of the stored procedures into a single query window.  This can be done easily through the GUI.  Once that is complete, I can easily do a “Find & Replace” on the table name and we’re done!

Ideally, those stored procedures are in source control already and you do your find-and-replace there, but sometimes you can’t control that.

Comments closed

Beware Statistics Corruption

Published 2015-12-16 by Kevin Feasel

Robert Davis shares a story of statistics corruption causing certain queries to fail:

I suspected that there was some difference between the queries that failed and the ones that were successful in SSMS. It ran the query they gave me, and I got the same error. I got disconnected and no further error info was returned. I also verified that the same query was successful on the otehr two tables mentioned. No errors on the other tables.

I wanted to know what error was causing the connection to be terminated, so I checked the SQL log and discovered that every time it failed, it was generating a stack dump. Before I was done investigating, it had generated 21 stack dumps. The key user-usable error info in the log was:

* Exception Address = 00007FF93BCF7E08 Module(sqlmin+00000000001E7E08)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000000

It turns out that CHECKDB & CHECKTABLE do not look at statistics.  If you find yourself in this situation, it’s not a bad idea to see if this is the cause.

Comments closed

Measuring IOPs

Published 2015-12-16 by Kevin Feasel

Joey D’Antoni shows us how to measure IOPs (I/O Per Second) on a SQL Server instance/server:

That handy SQL Server:Resource Pool Stats counter and it’s Disk Read IO/sec and Disk Write IO/sec provide you with the data you need to give your SAN admin. In this screenshot this is an Enterprise Edition instance, and you can see my resource pools on the left side—so if you are using resource governor, you could use this to classify IO workload by application for potential chargeback situations.

Very useful, and when combined with Resource Governor, can help you throttle I/O effectively (as opposed to wildly flailing in the general direction of a fix).

Comments closed

Visualizing SQL Saturday Data

Published 2015-12-16 by Kevin Feasel

Tamera Clark analyzes SQL Saturday Nashville data:

Select the funnel from the visualizations (1), select track in the field list (2) and drag track to the values box (3). (Image 5 below) Now we need to customize this visualization.  Select the paint brush to edit. (Image 6 below) I recommend giving each of the tracks a different color. Since Tracks are determined by the organizer the data maybe similar so you might want to use the same colors for more than one data point. You should also update the title Count of Tracks by Track sounds silly. Now we have a lovely display of session distribution by track.

She came up with a nice-looking set of information describing sessions and presenters for SQL Saturday Nashville 2016.  I love seeing this kind of thing and hope it becomes mainstream among SQL Saturday organizers (maybe to the point where some of this is built into the SQL Saturday website).

Comments closed

Fractals In SSMS

Published 2015-12-15 by Kevin Feasel

Slava Murygin gives us a script to generate fractals:

That is most difficult operation. At first, SSMS can’t show more than 5000 separate objects at the same time. In order to show more we have to construct “MULTIPOLYGON” or “GEOMETRYCOLLECTION”. That only the way to fit more objects into SSMS screen. However it is still limited.
In order to combine triangles in a single object we divide them in buckets (Line 106).
In this example I just making number of buckets approximately equal to a number of objects within each bucket. Making lower number of buckets will increase processing speed, but produce less colors. All objects in one collection will have the same color.
Also, I wrapped the last query in extra CTE to have more flexibility on results formation.

This is a fun post showing some of the power and limitations of geometry types in SQL Server and their display in SSMS.

Comments closed

Trace Flag 834 And Columnstore

Published 2015-12-15 by Kevin Feasel

Chris Bell warns us against having Trace Flag 834 turned on in an instance which contains columnstore indexes:

[I]t is not recommended to have trace flag 834 on when using columnstore indexes in your databases.

Since the 834 trace flag is a global level flag, and columnstores are in individual databases I wrote the script below to go through and check if you ave any columnstore indexes, and then check if the trace flag is enabled.

Chris also has a helpful script to see if your instance has this issue.

Comments closed

Check Your Automated Backups

Published 2015-12-15 by Kevin Feasel

Cody Konior shows us a few cases in which automated backup tools (like Ola Hallengren’s scripts) won’t actually back up databases:

We love Ola Hallengren’s Maintenance Solution but you should always always double-check either the msdb backup history or themaster.dbo.CommandLog table to make sure any important backup was taken. This is especially important if you trigger it manually and are relying on human input to get the parameters right.

Here are three easy to miss cases where the scripts won’t backup a database. These absolutely, definitely, aren’t bugs, they’re idiosyncrasies with the underlying backup command and (sometimes) how the script works. But they’re also much easier to miss in the verbose output of the script.

The moral of the story is to check your automated backup routines and make sure that they are doing what you expect.

Comments closed