Linchi Shea : Scripting

Transactional replication and massive data updates

Linchi Shea — Thu, 06 Oct 2011 04:19:00 GMT

SQL Server transactional replication has been a rather solid feature and works well for delivering data to reporting servers (among other things) in near real time. That said, it may not work too well when you need to perform massive updates on a published...(read more)

Scripting for the out-of-band attention

Linchi Shea — Sun, 21 Nov 2010 02:53:00 GMT

I have written a lot of throw-away scripts, some of which were written to control the execution of a large number of SQL scripts. The logic of these control scripts is often simple, but their execution can last for a long time (e.g. hours). When writing...(read more)

Logging for post-processing

Linchi Shea — Wed, 17 Nov 2010 19:31:00 GMT

As a practicing database professional, I'm sure you have done a lot of logging, I mean, output and/or error logging from your scripts, your scheduled jobs, and any other programs you might have written. The primary purpose of logging, of course, is to...(read more)

Add-Content and Out-File are not for performance

Linchi Shea — Mon, 04 Jan 2010 21:31:00 GMT

When you write Powershell scripts and need to write a text file, you have a number of ways to accomplish that. The often suggested approach is to use cmdlets Add-Content or Out-File. Well, this is not news. But some may not notice that these cmdlets are...(read more)

Get an overview report on your log shipping setups

Linchi Shea — Tue, 22 Dec 2009 02:45:00 GMT

[Updated Dec 23 to include a T-SQL script at the bottom of the post]. You may have an environment where log shipping is set up on various databases between various servers. Would it be handy to have an overview report highlighting which database is being...(read more)

Performance impact: Speeding up SMO script generation

Linchi Shea — Sun, 13 Dec 2009 22:18:00 GMT

Generating scripts through SMO can be as simple as walking down the database object tree and applying the Script() method to each scriptable object. Well, that is until you start to try it on a database that has a large number of objects (say a few thousands),...(read more)

Find the complete call tree for a stored procedure

Linchi Shea — Fri, 23 Oct 2009 03:11:00 GMT

Would it be nice to print out the complete call tree of a stored procedure? By complete call tree , I mean the following: · At the very top level, the call tree should identify all the procedures that are called directly. · For any given level, the next...(read more)

T-SQL Exercise: Simplest data-loading script to produce worst query performance – May 25th Update

Linchi Shea — Tue, 26 May 2009 12:52:00 GMT

This is a quick update on the T-SQL exercise I posted a few days ago. The goal was to write a simple T-SQL script to generate and load 4,000,000 rows into a table so that the following query would produce the worst performance, i.e. take longest time to finish:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

The original intent was to highlight some pitfalls in data loading that may lead to bad query performance. But then I thought why take all the fun away by having too many constraints, and why not just let it loose and see how bad it can get if one is to do it intentionally.

I must say that just looking at the scripts from Denis, Adam, and Tibor, I came to realize that things were not quite what I had thought they were.

For instance, I did not anticipate that a heap table could have performed the table scan so terribly in Tibor’s script. I thought that SQL Server would apply an allocation order scan and would optimize on the physical order regardless of forward pointers. Adam’s script surprised me with a freshly created clustered index. I thought that even if you could create a clustered index to push the limit on the fillfactor, the clustered index would defragment the data enabling SQL Server to scan the table efficiently, easily outweighing whatever gains (actually performance loss) you might get from lowering the page density.

So predicting the query performance turned out to be not very precise at all. It’s better to actually test these scripts out. I ran Adam’s script, Tibor’s script, and my script in a database whose data file was placed on an internal RAID-1 set (i.e. two mirrored physical drives). The internal drive (which was also the system drive C) was used to avoid dealing with the confounding factors because of SAN cache, storage virtualization, and so on. In addition, the test database was given 45GB for data and 20GB for log, significantly larger than what would be required during the tests.

All tests were done with SQL Server 2008 Enterpries x64 Edition (build 10.0.1600) on Windows Server 2003 Enterprise x64 Edition SP2 on an old HP ProLiant DL365 G1 with four cores and 4GB of physical memory.

Adam Machanic’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
create table test (
   x int not null,
   y char(896) not null default (''),
   z char(120) not null default('')
)

insert test (x)

select r

from

(

select row_number() over (order by (select 1)) r

from master..spt_values a, master..spt_values b

) p

where r <= 4000000

create clustered index ix_x on test (x, y)
with fillfactor=51

Tibor Karaszi’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
CREATE TABLE test (

x int NOT NULL

,x2 int NOT NULL

,y char(10) NOT NULL DEFAULT ('')

,z char(10) NOT NULL DEFAULT('')

)

DECLARE @rows int = 6666666, @toKeep int = 4000000, @diff int

INSERT test (x, x2)

SELECT TOP(@rows)

ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS r

,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 10 AS s

FROM master..spt_values a CROSS JOIN master..spt_values b

ALTER TABLE test ALTER COLUMN y char(892)

ALTER TABLE test ALTER COLUMN z char(100)

CHECKPOINT

DECLARE @rows int = 6666666, @toKeep int = 4000000, @diff int

DELETE TOP(@rows - @toKeep)
FROM test WHERE x2 IN(2, 4, 6, 8)

Note that Tibor’s script generated 3768021 rows instead of 4,000,000 rows in my tests.

Linchi Shea’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
create table test(
   c1 int,
   c2 int,
   c3 char(256) default ' ',
   c4 char(740) default ' ')

create clustered index cix_test on test(c2, c3)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 4000000

begin

insert test(c1, c2)

select @i,

case when @i % 2 = 0 then @i else 4000000 - @i end

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

To ensure that when the test table was created, the database was in the same state. The test table was dropped at the beginning of each script. And at that point, the database was empty with no user data.

After the data was loaded by each script, the test query (including DBCC DROPCLEANBUFFERS) was run three times, and each time the query elapsed time was logged. The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	123
Adam’s script – test run 2	121
Adam’s script – test run 3	120
Tibor’s script – test run 1	205
Tibor’s script – test run 2	207
Tibor’s script – test run 3	177
Linchi’s script – test run 1	408
Linchi’s script – test run 2	354
Linchi’s script – test run 3	358

Afterwards, for validation, the above entire process was repeated one more time. In other words, the test query was run six times for each data-loading script. The test query elapsed times were consistent with those recorded in the above table.

Now, in no way I’d claim that my script has managed to produce the worst query performance. I’m pretty sure that you can find a way to load data to produce much worse query performance.

In addition, as Adam and Tibor observed, the results may be dependent on many factors including the test system configurations. Although on a given test system the test results should be consistent, it could be misleading to compare the results across different test systems.

A case in point is that if you disable read-ahead reads entirely, the query performance would be much worse than any of the elapsed times posted above. So perhaps, your dataset can be loaded so that it induces SQL Server not to make effective use of read-ahead reads, and therefore get worse query performance.

I’m looking forward to seeing more scripts with worse query performance than the three scripts referenced in this post.

T-SQL Exercise: Simplest data-loading script to produce worst query performance

Linchi Shea — Fri, 22 May 2009 19:21:00 GMT

Here is a T-SQL scripting exercise in case you have a few minutes to spare or are bored with whatever else you are doing.

Objective

The task is to write a simple T-SQL script to generate and load 4,000,000 rows into a test table. The objective is to make the following simple test query to have the worst performance in terms of elapsed time:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

The longer the elapsed time, the worst it is.

Constraints

Of course, there must be a number of constraints, and they are as follows:

· The test table can have any number of columns of any fixed-length data types. However, the first column should be an integer column with values from 1 through 4,000,000, inclusive.

· The sum of the all the column widths are between 1000 bytes plus or minus 20 (i.e. between 980 bytes and 1020 bytes).

· No NULL is allowed for any column.

· No undocumented features are allowed in the script.

· When the test query is run, the test table must meet the following condition:

§ The test table has 4,000,000 rows.

§ The total size of data and index must be less than 7.5GB, as measured by sp_spaceused.

§ Avg. page density is greater than 50% as reported by DBCC SHOWCONTIG().

· The test query shown above must be run as is without any change.

Why?

Well, it’ll reveal a lot of about how data is stored and how the simple test query is processed.

If you would like to share your solution, and I hope you do, please post it here in the comment. Or if you want it better formatted, I can append it to the main text of this post.

How does that AD user account get access to the database?

Linchi Shea — Thu, 26 Mar 2009 05:19:00 GMT

Granting Active Directory (AD) groups access to a database can quickly become complex when the AD groups may nest many levels. Consider the following scenario:

You are requested to give Joe the same access to a database as John has. You look up John in the database, and find no trace of John. You then find a bunch of AD groups have been granted access to the database. Obviously, John is getting his access to the database through one or more of these AD groups. But exactly how does John get his access via the AD groups? In other words, what are the AD groups through which John gets his database access?

How would you approach this request? You can fire up the AD Users & Computers management console, look up the user account for John, find all the AD groups John is a member of, and then for each such group, recursively find all the groups this group is a member of.

Unfortunately, that is a labor intensive process. There is a better way.

First, note that you can use two AD utilities, DSquery and DSget, to recursively retrieve all the groups that John is a member of--either directly or indirectly--in a single line of command as follows:

cmd>dsquery user -samid John | dsget user -memberof -expand | findstr OU=Groups

In addition, you can run the following SQL query to find what AD groups have been granted access to the database:

select s1.name

from syslogins s1

join <your database>..sysusers s2 on s1.sid = s2.sid

and s2.hasdbaccess = 1

where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0

order by s1.name;

Now, if you compare these two results of the AD groups, and the ones that are common in both results are the AD groups through which John gains access to the database.

So far, all this work is still manual and labor intensive. But it’s not difficult to automate all the steps in a script. The following is a throw-away Perl script I wrote to do precisely that:

use strict;

my ($user_account, $server, $database) = (shift, shift, shift);

my $domain = 'NYD'; # domain name hardcoded. Change it, if necessary

my @groups = `dsquery user -samid $user_account | dsget user -memberof -expand | findstr OU=Groups`;

my @ADGroups;

# Extract the AD group names and prefix them with the domain name

foreach my $group (@groups)

{

if ($group =~ /\"CN=([^,]+)\,/)

{

@ADGroups = (@ADGroups, uc("$domain\\$1"));

}

my $sql = q/set nocount on;

select s1.name

from syslogins s1 join $database..sysusers s2

on s1.sid = s2.sid and s2.hasdbaccess = 1

where s1.isntgroup = 1 and s1.hasaccess = 1 and s1.sysadmin = 0

order by s1.name;

my $rc = `osql -S$server -E -n -h-1 -w200 -Q\"$sql\"`;

$rc =~ s/^\s*//;

$rc =~ s/\s*$//;

my @login_nt_groups = split /\s*\n\s*/, $rc;

my @LoginGroups;

foreach my $nt_group (sort {uc($a) cmp uc($b)} @login_nt_groups)

{

@LoginGroups = (@LoginGroups, uc($nt_group));

}

foreach my $group (getSetCommon([@ADGroups], [@LoginGroups]))

{

print "** $group\n";

}

sub getSetCommon {

my($setRef1, $setRef2) = @_;

my %temp;

my %common;

grep($temp{$_}++, @$setRef2);

map {$temp{$_} and $common{$_}++} @$setRef1;

keys %common;

}

The script takes three command-line parameters: the name of the AD user account (e.g. John in our case), the SQL instance name, and the database name. It retrieves all the AD groups that the user account is in (directly or recursively), retrieves the AD groups that have been granted access to the database, and the find the AD groups that are in both.

This is a quick and dirty approach that seems to work well so far. If you have a better approach, please share with us. Powershell solutions are welcome as well.

Executing a large number of SQL scripts

Linchi Shea — Fri, 05 Dec 2008 01:15:00 GMT

Say, you are given a large number of scripts underneath the following directory tree:

N:\Apps\Release\PRS\PR12345\CMP1\procs

\views

\triggers

\CMP2\procs

\views

\scripts

You need to execute these scripts on a SQL Server instance. To simplify the matter a bit, let’s assume that these scripts can be executed in any order. How would you go about it?

Here’s how I would do it with the following trivial throw-away Perl script:

# script file name: release.pl

$path = 'N:\Apps\Release\PRS\PR12345';

$server = 'SQLNYC01';

@scripts = `dir /B /s $path`;

foreach $f (@scripts) {

if ($f =~ /^(.+\.sql)\s*$/i) {

$osql = "sqlcmd.exe -S$server -E -w250 -i" . $f;

print "$osql \n";

# $log = `$osql`;

}

I then run this little script and re-direct the result to a file:

cmd>perl release.pl > PR12345.bat

PR12345.bat is the batch file that you can run to execute all the SQL scripts. Note that this Perl script assumes that all the SQL script files have the .sql file extension. That is just an example to show that you can easily filter out or filter for specific types of files, and construct the batch as you wish.

You can also easily execute the SQL script files directly in the Perl script without using an intermediate batch file. The commented line in the script does precisely that (if uncommented).

The assumption that the SQL scripts can be executed without any particular order can be easily removed with just a little bit of more coding as long as a way to identify the order is provided. For instance, a list of the script file names may be included in some release notes, and they must be executed in that order. It is quite easy to take that list, and help sort the SQL script files along with their complete paths so that they are listed correctly in the batch file.

Microsoft 2008 Winter Scripting Games

Linchi Shea — Tue, 19 Feb 2008 16:40:00 GMT

Microsoft is running its 3rd annual Winter Scripting Games. Three languages are allowed: VBScript, PowerShell, and Perl. The event sounds interesting. Check it out at:

http://www.microsoft.com/technet/scriptcenter/funzone/games/default.mspx

I didn't know about this event until somebody emailed me this morning. You bet I'll be looking into it, if not for anything else but to find whether there are compelling reasons for PowerShell :-)