Microsoft is now recommending diskspd. At this point, I suppose SQLIO could be considered “venerable” but tip a 40 on the curb for a good tool.
Comments closedCategory: Administration
John Martin shows us about dbo.suspect_pages:
dbo.suspect_pagesis a table that resides in the MSDB database and is where SQL Server logs information about corrupt database pages (limited to 1,000 rows) that it encounters, not just whenDBCC CHECKBis run but during normal querying of the database. So if you have a DML operation that accesses a corrupt page, it will be logged here, this means that you have a chance of identifying a corruption in your database outside of the normal DBCC CHECKDB routine.
This is a nice tool we can use to check for corruption.
Comments closedMark Broadbent had to increment all of his sequences by 10,000. Here’s how he did it:
The only problem with this approach is that our database was configured (rightly or wrongly) with approximately 250 sequences! Since we could not be sure which sequences would ultimately cause us problems we decided to increment each one by 10,000.
Not being someone who likes performing monotonous tasks and also recognising the fact that this task would probably need to be performed again in the future I decided to attempt to programmatically solve this problem.
The script isn’t too difficult to understand but let me reiterate his warning: read the script before you run it, and know exactly what it’s doing before you run it.
Comments closedMark Broadbent shows us how to restore databases with Change Data Capture enabled:
This automatically poses the question of how it is possible to restore a backup chain with CDC? On a database restore, in order to apply differential backups and transaction logs the NORECOVERY clause is required to prevent SQL Server from performing database recovery.
If this option is required but KEEP_CDC in conjunction with it is incompatible, surely this means point in time restores are not possible for restores that require CDC tables to be retained?
-Wrong!
The answer is a bit surprising, and my guess is that most database administrators are totally unaware of this restoration quirk.
Comments closedPriyanka Chouhan talks about identifying and handling blocked processes:
In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.
Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:
My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.
Comments closedWindows Management Framework 5.0 is available, says Max Trinidad:
Finally! The Windows Management Framework version 5.0 RTM is available for download for all down level Operating systems: Windows 7, Windows 8.1, Windows Servers 2008 R2, Windows Server 2012, and Windows Servers 2012 R2.
There are several interesting features here. My favorite one is “Just Enough Administration (JEA)”; after all, who wants too much or too little administration?
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 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 closedAndrea Allred shows one way of removing a bad query plan:
If you click on the query_plan link, you can see what the plan looks like. After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.
DBCC FREEPROCCACHE normally is something you don’t want to play with in production, but this is narrowly focused enough not to harm you down the line.
Comments closedAllan Hirt prepares us for a licensing letdown:
Say Hello to Core-based Licensing for Windows Server
This is the one that may annoy most folks. Like SQL Server, Windows Server 2016 licensing will be core-based, including the Core Infrastructure Suite SKU. Historically, Windows pricing has been MUCH lower than SQL Server, and no prices have been announced. So before anyone has a conniption, let’s see what the core pricing will be based on the chart shown on page 2, there are cases where the cost may be the same as it is today.
I’m now curious about how many people will hit a wall with Windows Server editions like we’ve seen with SQL Server 2008 R2.
Comments closed