SQL Server Auditing PII Data, Looking for Email Data

One of the key aspects of being a DBA is security, and what used to be just protecting your data from unwelcomed intruders through object protection has morphed into the data inside those objects.  For example protecting PII (Personal Identifiable Information).  I know what you’re thinking, if the tables and the interfaces (Sprocs, triggers, views, etc) are protected then that should suffice, and in a perfect world it would be.  However we don’t live in a perfect world, so for example what if you need to move data into a test or investigation environment where access to data is much more liberal, this data needs to be protected.  If you’re lucky enough to have every column documented in your DB on what is PII I have great admiration for your companies standards, unfortunately this is a small percentage of companies.  With an army of developers and 100s of DB’s we needed a way to audit our data before shipping the data back to our developers in test.  One of the key components of PII data are email data, so I wrote the following script to ensure there are no public emails in the data we ship.  Note we scrub the data before moving it, this is just an audit process.  If you need to scrub your data the script below can be modified.  One note I use the sample option in the statement, because we have tables with billions of records and rather than scanning the entire table I only look at a subset of the data.

declare @stmt varchar(1000)

Declare @TName varchar(50)

Declare @ColName varchar(50)

if object_id('tempdb..#PII') is not null Drop table #PII

create table #PII

(Tablename varchar(100), ColumnName varchar(100))

declare DiscoverCursor cursor for

select c.name, t.name from sys.syscolumns  c

inner join sys.tables t

on object_id = id

where xtype in(35, 99, 167, 175, 231, 239)

Open DiscoverCursor

Fetch Next from DiscoverCursor

into @colname, @tname

While @@Fetch_status = 0

Begin

              set @stmt = 'if exists(select top 5 * from '+  @tname + ' tablesample (10000 rows) where ' + @colname + ' like ' + '''' + '%@yahoo.com%' + ''''  +

' or  ' + @colname + ' like '   + '''' + '%@gmail.com%' + '''' +

' or  ' + @colname + ' like '   + '''' + '%@hotmail.com%' + ''''  + ')' +

'Begin

Insert into #PII values(' + '''' +   @colname +  ''''  +   ', ' + '''' +  @TName + '''' + ')

 end'

             

      

       exec (@stmt)

       print @stmt

       Fetch Next from DiscoverCursor

       into @colname, @tname

End

Close DiscoverCursor

Deallocate DiscoverCursor

select * from #pii

SQL Server Shrinking Datafiles and Reindexing, Breaking the Cycle

This post is in relation shrinking data files in SQL Server not the log file. They are two completely different beasts, shrinking your log file has little to no impact on your db, where shrinking data files has the potential to put you in a world of hurt. The pain you feel isn't the actual data shrinking, thats relatively harmless to the db, its the after effects. When you shrink a data file it moves all the filled data pages to the front of your data files, and takes the empty unused data files and puts them at the end, truncating the empty space as its last act.

What happens here is fragmentation of most of your indexes, particularly your large more dynamic indexes. When your data is fragmented bad query plans occur, because SQL determines its least expensive to do scans rather then expensive seeks or in particular partial scans on fragmented indexes. In order to solve this problem, you need to rebuild your fragmented indexes. However to rebuild indexes SQL needs to carve out a significant amount of space in the data file for the new rebuilt index. The result is all the space you got back in the shrinking process will be used in index creation, and often your back where you started or even worse off.

This is the primary reason you here all over the net, don't shrink your data files unless you need to. So the question is when is shrinking appropriate? Deletion of data is the primary reason you would want to shrink a data. A great example is some type of journaling or audit table that might have been taking inserts and or updates for a year or multiple years. After the exploding growth it's determined that it can be trimmed, and moving forward a regular trim process is put in place. However after the initial trim a significant amount of space in the datafile is unallocated, and will never be used. For if example 50% of your datafile is unused it makes sense to shrink. Unfortunately even in this situation you'll end up with the same issue as above shrinking and reindexing without gaining much ground.

The solution here is recreate the indexes of the large trimmed tables to another datafile, using a separate filegroup. When you recreate this index, it leaves the entire sum of the old index empty in the old data file. This gives you enough room to shrink and reindex, so that you can gain back significant amount of space. Before going into the procedures I need to warn add a warning that this should be done during a maintenance window, recreating an index will lock access to the table, for both reads and writes, including dirty reads. Even if your using Enterprise Edition which allows for online rebuilds, this is not a rebuild it's actually dropping and recreating index.

Procedures

1. Identify Large Tables
2. Create a filegroup for each table from step 1. I prefer creating 2 files per filegroup, for flexibility reasons in the future.
3. For each of the clustered indexes on the tables identified use the create index with drop existing clause. In the example below I'm recreating a PK, so specifying "Unique clustered" not only maintains unique properties but also maintains any relationships that reference the index.

Example

create unique clustered index PK_T1_Index on

t1(f1)

with (drop_existing = on, sort_in_tempdb = on) on indexfg

1. If you have other large indexes other then the clustered index, you might want to add them to the new filegroups as well. The more space you free up in the original file, the better off you are
2. Once the indexes are moved, shrink the old datafile. Here you should be able to recapture plenty of space.
3. After shrinking, then reindex any of the fragmented indexes in the original datafile. iSince these are smaller any space added from the reindex will be minimal.

Maintaining multiple named environments with cross database objects

A common issue we have is maintaining multiple environments, when using cross database objects such as stored procedures, views and functions. An example would be having a source oltp db called Maindb, that gets pushed to different env's with the names of test_Maindb, staging_Maindb, and warehouse_maindb, etc. Now lets say there's a corresponding database for each of these databases called Secdb, test_Secdb, etc. Within the Secdb you might have stored procedures or views that reference the maindb such as the below statement.

create procedure exampleSP

as

select * from Maindb.dbo.table1

Now every time Maindb goes to a different environment the sproc needs to be recompiled to reference the new name of the db for example in test the sproc would look like this:

create procedure exampleSP

as

select * from test_Maindb.dbo.table1

This can become fairly cumbersome to change manually. To get around this the following script allows you change all the reference names of all the logical objects within the db.

Declare @orgDBName varchar(100)

Declare @NewDBName varchar(100)

set @orgDBName = 'Maindb'

set @NewDBName ='Test_Maindb'

Declare @FindQuery varchar(2000)

Declare @Stmt nvarchar(max)

set @FindQuery = 'select definition from sys.all_sql_modules where definition like ' + '''' + '%' + @orgDBName + '%' + ''''

+ ' and object_id > 0 '

create table #TempDef

(Def nvarchar(max))

insert into #TempDef

exec (@FindQuery)

update #TempDef set Def = REPLACE(def,'Create Procedure', 'Alter Procedure')

update #TempDef set Def = REPLACE(def,'CREATE VIEW', 'Alter View')

update #TempDef set Def = REPLACE(def,'CREATE FUNCTION', 'Alter FUNCTION')

update #TempDef set Def = REPLACE(def,@orgDBName + '.', @NewDBName + '.') from #TempDef

Declare UpdateCursor cursor for

Select def from #TempDef

Open UpdateCursor

Fetch Next from UpdateCursor

into @Stmt

While @@Fetch_status = 0

Begin

--print (@stmt)

exec (@stmt)

Fetch Next from UpdateCursor

into @stmt

End

Close UpdateCursor

Deallocate UpdateCursor

Mysql Gathering Queries per Second

One of the key questions asked of any DBA managing a database is how many queries per sec are there against a server. Mysql exposes the queries and questions status counters which you can use to get this data. However before I get into the script I must explain the difference. Questions are the amounts of calls that have been executed against the server while queries are the amount of statements. In most implementations where routines such as procedures aren't used these will be the same, since every call to the database will only be a single statement. However since routines can and often contain more then one statement your queries counter will differentiate from your questions counter. In all the scripts below I key off questions, since my concerns is calls coming into the server.

This first procedure is very simple and provides a snapshot in time of how many calls per second your server is taking. By passing a wait time variable in, you tell mysql to delta the start and end, and divide by the wait time

Drop Procedure if exists ;

DELIMITER //
CREATE PROCEDURE mysql.QuestionsPerSec(WaitTime int)

BEGIN

Declare StartCount int;

Declare EndCount int;

select VARIABLE_VALUE into StartCount from information_schema.global_status where variable_name = 'Questions';

select sleep(WaitTime);

select VARIABLE_VALUE into EndCount from information_schema.global_status where variable_name = 'Questions';

Select cast((EndCount - StartCount) as decimal(10,3)) /WaitTime as 'Queries Per Second';

END //

DELIMITER ;

Example Call

Call mysql.QuestionsPerSec(10);

Grabbing how many calls per sec has its place during unexpected traffic peaks or data issues, but if you don't know what your baseline is, the number is meaningless. In order record this information, use the following sproc. Notice it creates a performance database, as well as a table if they don't exist. Once you have the table in place, how you want to parse and analyze is up to you.

Drop Procedure if exists mysql.RecordQuestionsPerSec;

DELIMITER //
CREATE PROCEDURE mysql.RecordQuestionsPerSec(WaitTime int)

BEGIN

Declare StartCount int;

Declare EndCount int;

CREATE DATABASE IF NOT EXISTS PerformanceHistory;

CREATE Table IF NOT EXISTS PerformanceHistory.QuestionPerSec (QuestID int primary key Auto_Increment, QuestionsPerSecond decimal(10,3), DateTaken datetime) engine = myisam;

select VARIABLE_VALUE into StartCount from information_schema.global_status where variable_name = 'Questions';

select sleep(WaitTime);

select VARIABLE_VALUE into EndCount from information_schema.global_status where variable_name = 'Questions';

insert into PerformanceHistory.QuestionPerSec(QuestionsPerSecond, DateTaken)

Select cast((EndCount - StartCount) as decimal(10,3)) /WaitTime as 'Queries Per Second', now();

END //

DELIMITER ;

Executing system stored procedures with a lower privileged user in SQL Server

We have a workflow that does a significant amount of data modification. Later during the day a second process will read the db where much of the data has change. However the issue we found was sql server was not updating it's statistics, and causing poor plans during the reads. Once stats were updated, plans returned to normal. The solution was to have our java client update stats using sp_updatestats before it started the reads. However since the user is fairly restricted and system sprocs such as this require elevated privileges, we would get the following error.

"Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15

User does not have permission to perform this action."

To get around this we encapsulated this call within a user defined stored procedure, leveraging the Execute as option and executing as dbo. The example would look like this.

create procedure updatestatistics

with execute as 'dbo'

as

Mysql slow query log Not filtering correctly

If you setup mysql's slow query log you might notice it not filtering on the value setup up for the global variable long_query_time. For example maybe you have this setup for a value of 2 seconds, but are seeing durations of 0 or 1 seconds being recorded. The most likely reason for this is the variable "log_queries_not_using_indexes" is turned on by default and this uses the same log file or log table as the slow_query_log.

Get Job_ID Function in MSDB

At zillow we run over 1000 dataflow tasks through out job server. For the DBA group this means spending a good percentage of our time living in the msdb database, particularly in the sysjob tables. When we need to query for particular history of a job, or looking for particular job step, it requires an inner join to the sysjobs table. When you do this over and over it becomes cumbersome. To make our life a bit easier, we've written the following function that returns a job_id when a job name is passed in. I would think MS would have a canned version of this, but couldn't find one, but that just might be my google skills.

Function Definition

Use Msdb

go

create function dbo.GetJobID (@JobName varchar(500))

returns nchar(36)

with execute as caller

AS

Begin

Declare @Job_id nchar(36)

select @Job_id = job_id from msdb.dbo.sysjobs

where name = @JobName

Return(@Job_id)

End

Example of Use

select * from sysjobhistory where job_id = dbo.getjobid('test')

order by instance_id desc