Press "Enter" to skip to content

Category: Administration

Using Startup Stored Procedures

Published 2017-08-02 by Kevin Feasel

John Morehouse explains how to set up a startup stored procedure, as well as some of the risks involved:

Startup procedures automatically execute whenever SQL Server is started.  Where would you use this?  One example is if you had an application that required the use of a global temporary table, you could use a startup procedure to create it.  This would allow the table to be immediately accessible to any application that requires it.

Another possibility is that you could “warm up” the cache so that data is already cached when an application needs it.  This would help prevent that initial I/O hit the first time the query is called, thus potentially improving initial performance.

Click through for details.  I’ve created a couple of these, but they rarely come to mind and that’s one of the big problems:  if there is an issue, there’s probably quite a bit of investigative work that would take place before somebody remembers that hey, these things exist.

Comments closed

Moving SQL Server Data Files

Published 2017-08-01 by Kevin Feasel

Jana Sattainathan walks us through the process of moving a SQL Server data file from one drive to another:

Space got tight on a drive and I knew that there was space on another drive. I had already set this particular database with multiple secondary file groups/files (.ndf files) instead of a huge and single .mdf file (which would have made the whole thing a lot harder).

The procedure to relocate data files is extremely simple

Click through for Jana’s seven salvos of administrator success.

Comments closed

Filesystem Enumeration DMV

Published 2017-08-01 by Kevin Feasel

Erik Darling points out a new DMV in SQL Server 2017:

SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command, with some filtering.

The addition of a search filter is particularly nice, since the dircommand isn’t exactly robust in that area. If you’ve ever wanted to filter on a date, well… That’s probably when PowerShell got invented.

If I run a simple call to the new function like so…

Click through to see Erik use the new function.

Comments closed

Troubleshooting MSDTC

Published 2017-07-31 by Kevin Feasel

Jeff Mlakar has a troubleshooting guide for the Distributed Transaction Coordinator:

Microsoft Distributed Transaction Coordinator (MSDTC) is a Windows service that coordinates transactions spanning multiple databases. It is a transaction manager that allows applications to include several different sources of data in one transaction. MSDTC coordinates committing the distributed transaction across all servers enlisted in the transaction.

An example here would be a process on one machine calling a stored procedure on another machine which in requires data that changes together in a transaction.

The MSDTC service is running on each of the servers to manage the successful commit (or rollback) of the transaction across all servers enlisted in the transaction.

My MSDTC experience is generally negative, that this is more trouble than it’s worth.  But sometimes you’ve got to use it, and when you do, it’s nice to have the skinny on it.

Comments closed

Moving TempDB In Linux

Published 2017-07-28 by Kevin Feasel

David Klee shows how to migrate the tempdb database when running SQL Server on Linux:

We previously created a folder at /var/opt/mssql/data/tempdb01 for these files. Moving them is straightforward, once you know the file system structure. The following commands will move them to the new location, and I also add additional files to equal the four vCPUs I have on this SQL Server VM. The file growth is my model database’s default of 64MB for this instance. Do as you would normally do with SQL Server on Windows with tempdb file counts and separation of duties for your workload.

Read on for the process.  As a general spoiler, the “how to do this in Linux” answer is usually pretty close to the same as the “how to do this in Windows” answer, at least once you get into Management Studio.

Comments closed

The Risks Of Clearing The Procedure Cache

Published 2017-07-28 by Kevin Feasel

Erin Stellato explains two downsides to running DBCC FREEPROCCACHE or anything else which clears query plans:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

There are reasons to run these commands, but ideally, you should be as precise as possible in clearing plans out of the cache.

Comments closed

Sharepoint And MAXDOP=1

Published 2017-07-27 by Kevin Feasel

Daniel Glenn has a public service announcement on Sharepoint maximum degree of parallelism:

For my first install test, I was using a service account that did have administrative rights on SQL as well. I looked at the setting in SQL and SharePoint 2016 did change the Maximum Degree Of Parallelism setting to 1. So, the story is, as of now anyway (we are dealing with Preview software), SharePoint Server 2016 requires MAXDOP=1.

Kidding about Sharepoint aside, if you do have Sharepoint in your environment, it’s worth knowing how to configure it correctly.

Comments closed

Tracking DDL Events

Published 2017-07-27 by Kevin Feasel

Kenneth Fisher has a simple database trigger to track certain data definition language events:

A couple of notes before testing the code. The event groups I’m using will pull CREATE, ALTER and DELETE events for those objects. For a more complete list of events (you might want to add service broker events for example) go here. Also I’m using ORIGINAL_LOGIN because it will return who made the change even if they are impersonating someone else.

For my test, I created a user that only has db_DDLADMIN on the database. That means it can make DDL changes but can’t insert, update, delete or even run a select against any table in the database. That’s why I grant INSERT to public for the logging table.

It’s a good way of knowing when unexpected changes happen, too.

Comments closed

Reading Drive Files Recursively From T-SQL

Published 2017-07-25 by Kevin Feasel

Slava Murygin shows how to use a common table expression to read a recursive drive listing:

In this blog I’ll use undocumented in SQL Server extended stored procedure “xp_dirtree“.
That procedure is well described in Patrick Keisler’s blog.

Briefly, “xp_dirtree” extended procedure returns tree of sub-directories from given folder and has three parameters:
1. Starting or root folder
2. Depth level – determines how deep SQL Server will dig into a sub-directory structure. If it set to zero or omitted whole tree will be returned.
3. If omitted or set to zero will return only folders .If set to “1” will return files as well.

If you want to be really mean to somebody, create a shortcut to the parent directory within a directory.

Comments closed

Managing Drives On Linux

Published 2017-07-25 by Kevin Feasel

David Klee walks through some basics of Linux administration with respect to drives and mountpoints:

We see that all four of the drives show up in the list. Because of the nature of how I presented the disks to the VM, the bootable drives (sdd) show up at the end of the chain instead of the beginning, but that’s OK. It doesn’t change how the disks are bootable.

We are going to use the Logical Volume Manager (LVM) to manage the disks for us instead of using regular partitions. On Windows-based servers, Windows has the ability to expand partitions without incurring any downtime, and we want to have the same flexibility from a Linux standpoint. With elevated priviledges (for the rest of the commands), let’s scan the drives to look at what it sees.

Read the whole thing.

Comments closed