Thursday, October 26, 2017

DBA Categories from Brent Ozar

Brent Ozar shared a post on the categories for a SQL Server DBA.
NOTE that these overlook things like BI or data scientist professionals, which are not usually considered part of the DBA span of work

https://www.brentozar.com/archive/2017/09/hire-junior-dba/

The most interesting part, to me, was this chart:
(Credit - BrentOzarUnlimited.com)

I like the way he breaks this up to Database Developer, Development DBA, and Production DBA.
This seems to reflect the reality that I see in our own business as well as in some others.

Wednesday, October 04, 2017

MS SQL Server Max Memory Size for given Server Physical Memory

Glenn Berry, over at SQL Server Central, has written an excellent and straight-forward guide to initial settings for max memory in SQL Server, given the amount of memory available on the server.

Suggested Max Memory Settings for SQL Server 2005/2008

I would encourage you to visit his article for details, but here follows the crux of the matter:
Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

Again, note that he goes on to give more advice on tuning with additional products, and how much memory should be available to the system when under max load. I would encourage you to check out the original article.


Friday, September 15, 2017

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.

For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.

However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.

This can be done in all 3 of the major RDBMS platforms.  Links below:

MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.

This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.

If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)

Wednesday, May 03, 2017

I had previous written very breifly about creating a report on disk space using WMI

A much better, and very concise instruction for querying disk information (including %free) can  be found here:
https://msdn.microsoft.com/en-us/library/aa394173(v=vs.85).aspx
This could be easily adapted to be driven from a list of servers in a text file, in a query, or received from something like a net view command

Write-Host "Drive information for MY_Server"

Get-WmiObject -Class Win32_LogicalDisk -ComputerName MY_Server |
    Where-Object {$_.DriveType -ne 5} |
    Sort-Object -Property Name | 
    Select-Object Name, VolumeName, FileSystem, Description, VolumeDirty, `
        @{"Label"="DiskSize(GB)";"Expression"={"{0:N}" -f ($_.Size/1GB) -as [float]}}, `
        @{"Label"="FreeSpace(GB)";"Expression"={"{0:N}" -f ($_.FreeSpace/1GB) -as [float]}}, `
        @{"Label"="%Free";"Expression"={"{0:N}" -f ($_.FreeSpace/$_.Size*100) -as [float]}} |
    Format-Table -AutoSize

OUTPUT:
Drive information for MY_Server

Name VolumeName FileSystem Description             VolumeDirty DiskSize(GB) FreeSpace(GB) %Free
---- ---------- ---------- -----------             ----------- ------------ ------------- -----
A:                         3 1/2 Inch Floppy Drive                        0             0      
C:              NTFS       Local Fixed Disk        False               59.9          5.83 9.74 
D:   Programs   NTFS       Local Fixed Disk        False                200         18.03 9.02 
E:   Product_1  NTFS       Local Fixed Disk        False                250         99.57 39.83
F:   Product_2  NTFS       Local Fixed Disk        False                 20          7.02 35.09
R:   Product_3  MVFS       Network Connection                         78.13         48.83 62.5 


Tuesday, March 21, 2017

Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :)
The following article has a great table that defines when operations will be minimally logged.
https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

Here's the table, just in case the link goes away at some point in the future:

Table Indexes

Rows in table Hints Without TF 610 With TF 610 Concurrent possible
Heap
Any
TABLOCK
Minimal
Minimal
Yes
Heap
Any
None
Full
Full
Yes
Heap + Index
Any
TABLOCK
Full
Depends (3)
No
Cluster
Empty
TABLOCK, ORDER (1)
Minimal
Minimal
No
Cluster
Empty
None
Full
Minimal
Yes (2)
Cluster
Any
None
Full
Minimal
Yes (2)
Cluster
Any
TABLOCK
Full
Minimal
No
Cluster + Index
Any
None
Full
Depends (3)
Yes (2)
Cluster + Index
Any
TABLOCK
Full
Depends (3)
No

DBA Categories from Brent Ozar

Brent Ozar shared a post on the categories for a SQL Server DBA. NOTE that these overlook things like BI or data scientist professionals, w...