A Fine Slice Of SQL Server
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 closedAllan 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 closedMonica 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 closedRobert 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 closedJoey 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 closedTamera 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 closedSlava 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[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 closedWe love Ola Hallengren’s Maintenance Solution but you should always always double-check either the msdb backup history or the
master.dbo.CommandLogtable 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 closedReza Rad shows us how to introduce workflow concepts into MDS 2016:
This feature introduced in SQL Server 2016 CTP 3.0. You can save your pending changes in a set called Change Set. This Set then can be modified or deleted or applied. You can also add the functionality of Requirement for Approval for the change set, which then requires an entity administrator to approve or reject the change set. This is a great feature for MDS and would help on the work flow process of making changes in master data. Let’s now have a closer look at how this feature works in action.
I don’t use Master Data Services, but I could see this being very useful at a large company with a lot of people modifying master data.
Comments closed