Estimating Query run times with QUERY_GOVERNOR_COST_LIMIT

Ive blogged on this subject before when trying to make a reindexing decision about whether to reindex an object online or offline based on the cost. https://dbamohsin.wordpress.com/2011/02/08/query-control-with-query_governer_cost_limit/

Its easy enough to apply the same theory for cost onto any query. I do this to give me a basic estimate of how long a query will take on a certain server.

I do this by setting a Query limit of 1 and then executing the query. If it is an intensive query then it will almost certainly fail and give you a cost estimate

SET QUERY_GOVERNOR_COST_LIMIT 1
GO
select * from this_is_my_big_table

The result is something like this:

Msg 8649, Level 17, State 1, Line 1
The query has been canceled because the estimated cost of 
this query (526) exceeds the configured threshold of 1. 
Contact the system administrator.

This immediately tells me that running the SQl for real is likely totake around the 10 minute mark (526 Seconds)

Note that cost is dependent on server hardware and configuration, running the same query on a test server produced the following output:

Msg 8649, Level 17, State 1, Line 1
The query has been canceled because the estimated cost of 
this query (694) exceeds the configured threshold of 1. 
Contact the system administrator.

Find LOB Columns Script

Scans all tables in a SQL Server database and list the columns which are large objects (TEXT, NTEXT, ,IMAGE VARCHAR(MAX), NVARCHAR(MAX), XML, VARBINARY)

I wanted this as i wanted to identify which tables i couldn’t reindex online

SELECT 
    c.object_id, 
    OBJECT_NAME(c.object_id) as [Object Name], 
    c.name as [Column Name], 
    t.name as [Column Type]
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id 
WHERE c.object_id in 
    (SELECT object_id FROM sys.objects 
     WHERE type_desc = 'USER_TABLE')
AND (t.name in ('image', 'text', 'ntext', 'xml')
OR (t.name in ('VARCHAR', 'NVARCHAR', 'VARBINARY') AND c.max_length = -1))

The same can also be one via information_schema views:

SELECT * 
FROM information_schema.columns
WHERE TABLE_NAME IN 
    (SELECT table_name 
     FROM information_schema.tables 
     WHERE table_type = 'base table')
AND (DATA_TYPE in('text','ntext','xml','image')
OR  (DATA_TYPE in('varchar','nvarchar','varbinary')
AND CHARACTER_MAXIMUM_LENGTH = -1))
ORDER BY TABLE_NAME

You must use the Role Management Tool to install or configure Microsoft .NET Framework 3.5

When trying to Install SQL Server 2008 on a Windows Server 2008 R2 machine, you get the following error:

image

This error happens because Windows 2008 R2 ships with .NET 3.5.1 Framework. To install you need to enable the feature via Server Manager.

image

This error will also happen with other Microsoft products that require .NET Framework as a Pre-Requisite such as Visual Studio 2008.

Creating Self-Signing Certificates and assigning to Powershell scripts

If we take the assumption that you’ve just started out with powershell, you have probably seen this error when attempting to run a script:

File C:\scripts\test.ps1 cannot be loaded because the execution of scripts is 
disabled on this system. Please see "get-help about_signing" for more details.

Probably after some research and looking into about_signing you may have run the following in your Powershell editor:

Get-ExecutionPolicy

and found that your machine was set to be restricted. This policy prevents any scripts from being run in your environment. The next thing you probably did was set your execution policy to be Unrestricted, which then allows any scripts to be run without any security checks.

This would have solved your immediate problem of getting the script to run, however, to create more control over what can run and what cant, you can introduce certificates.

Self-Sign Certificates

To create Self-Signed ceriticates, the makecert.exe tool needs to be installed. This ships as part of the .NET Framework SDK (1.1 or higher) – i have 2.0 installed (found here)

To make the certificate:

C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin>makecert -n "CN=PowerShell Local Certificate Root" -a sha1 -eku 1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer -ss Root -sr localMachine

Succeeded

C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin>makecert -pe -n "CN=PowerShell User" -ss MY -a sha1 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk -ic root.cer

Succeeded

After running these commands you will be prompted to provide passwords.  There are a number of ways to determine if the cert was created correctly including looking in the Certificated snap-in MMC (mmc.exe | add snap-in | certificates)

image

or by running the following PowerShell command (I have narrowed down gci to just return where the subject is Powershell as i have several different ceritifcates):

Get-ChildItem cert:\CurrentUser\My | Where-Object {$_.subject -match "Powershell"}

Directory: Microsoft.PowerShell.Security\Certificate::CurrentUser\My

Thumbprint                                Subject

———-                                ——————-

52F872D96D6A507846875CE83317C99F53E5C48F  CN=PowerShell User 

Sign the script

The last step is to sign the script using the newly created certificate:

$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning `
  | Where-Object {$_.subject -match "Powershell User"})[0]

  Set-AuthenticodeSignature "\\Path\myscript_New.ps1" $cert

SignerCertificate                         Status       Path

—————–                         ——       ——–

52F872D96D6A507846875CE83317C99F53E5C48F  Valid        myscript_New.ps1

Troubleshooting

If your having issues with signing the script then see this – UnknownError when using Powershell ISE to Set-AuthenticodeSignature

UnknownError when using Powershell ISE to Set-AuthenticodeSignature

This is discussed on Microsoft Connect here

When attempting to assign a certificate to a powershell script, it does not assign and gives a status of ‘UnknownError’

$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning | Where-Object {$_.subject -match "Powershell User"})[0]
Set-AuthenticodeSignature "\\path\myscript.ps1" $cert

SignerCertificate  Status          Path 
—————–  ——          —- 
                   UnknownError    myscript.ps1

This happens because when using Powershell ISE the default encoding is ‘Unicode Big Endian’

If the file is recreated using UTF-8 then the script is correctly assigned a certificate

#recreate the script into a new file
type "\\Path\myscript.ps1" | out-file "\\Path\myscript_New.ps1" -encoding utf8
$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning `
   | Where-Object {$_.subject -match "Powershell User"})[0]
#use the UTF-8 encoded file
Set-AuthenticodeSignature "\\Path\myscript_New.ps1" $cert

SignerCertificate  Status          Path 
—————–  ——          —-  
Cert_Thumbprint    Valid           myscript_New.ps1

Aggregation & mapReduce in Mongo

In Oracle, or SQL Server or any other RDBMS, SQL makes it very easy to do aggregation by using GROUP BY.

SELECT make, count(model) FROM Vehicle
GROUP by make 
order by make

In Mongo, Its slightly more complex to build group by aggregation from collections. There are many ways of getting different counts out, and some are explored below.

DISTINCT

//Distinct all Makes of cars in Collection
db.vehicle.distinct('model');

The above is the same as doing a Select Distinct in SQL. The data returned is all distinct types of vehicle models in the collection

COUNT

//Count all Ford Cards
db.vehicle.count({make:"FORD"});

The above will count all ford cars in the vehicle collection

GROUP

Generally only applicable for collections of up to 10,000 documents. Errors have been reported if attempted on larger sets. Use mapreduce for larger data collections.

// Group by make
db.vehicle.group(
           {key: { make:true},
            reduce: function(obj,prev) { prev.count++;},
            initial: { count: 0 }
            });

The result is something a bit like this:

{

        "make" : "ALFA ROMEO",

        "count" : 25

},

{

        "make" : "CORVETTE",

        "count" : 1

},

{

        "make" : "HONDA",

        "count" : 2

},

The collection is being aggregated at the make level. The reduce function is passing a aggregated count back to the object using the key criteria. This can be further enhanced by adding in the model document

// Group by make, model
db.vehicle.group(
           {key: { make:true, model:true},
            reduce: function(obj,prev) { prev.count++;},
            initial: { count: 0 }
            });

The result is something like this:

{

        "make" : "ALFA ROMEO",

        "model" : "MITO",

        "count" : 2

},

{

        "make" : "ASTON MARTIN",

        "model" : "VIRAGE",

        "count" : 1

},

{

        "make" : "CORVETTE",

        "model" : "C6",

        "count" : 1

},

The above 2 example use a cumulative count:

prev.count++;

but the same can also be achieved by doing:

prev.count += 1

MAP REDUCE

Map-reduce is made up of two operations. The map part formats the data into key-value pairs, and the reduce builds the data set.

Eg: we want to return the count of the number of values for each type of make of vehicle.

//Map Function
var map = function() {
    emit( this.make, {model: this.model} );
  }

Create the MAP function. We are essentially saying return me the make of vehicle, but for that vehicle, my aggregator is the make document.

//Reduce Function
//Add up all the votes for each key.
var reduce = function(key, values) {
  var sum = 0;
  values.forEach(function(doc) {
    sum += 1;
  });
  return {model: sum};
}

Create the REDUCE function. the MAP values are automatically passed into the reduce function. Note, that at this stage we havnt actually specified the collection we want to run this mapReduce on.

//var into output **CAREFUL** - saves it as a new collection 
//would overwrite existing collection
var op = db.vehicle.mapReduce(map,reduce, {out : "myoutput"} );

The above saves the results of the mapReduce into a myoutput collection.

//return results
db[op.result].find();

By doing the above, the results are returned to screen.

PRIMARY> db[op.result].find();

{ "_id" : "ALFA ROMEO", "value" : { "model" : 25 } }

{ "_id" : "ASTON MARTIN", "value" : { "model" : 48 } }

{ "_id" : "AUDI", "value" : { "model" : 33 } }

{ "_id" : "BENTLEY", "value" : { "model" : 8 } }

{ "_id" : "BMW", "value" : { "model" : 32 } }

{ "_id" : "CHRYSLER", "value" : { "model" : "YPSILON" } }

{ "_id" : "CITROEN", "value" : { "model" : "C4 PICASSO" } }

{ "_id" : "CORVETTE", "value" : { "model" : "C6" } }

{ "_id" : "FIAT", "value" : { "model" : 9 } }

{ "_id" : "FORD", "value" : { "model" : 14 } }

{ "_id" : "HONDA", "value" : { "model" : 2 } }

{ "_id" : "HYUNDAI", "value" : { "model" : 5 } }

{ "_id" : "JAGUAR", "value" : { "model" : 3 } }

{ "_id" : "KIA", "value" : { "model" : 2 } }

{ "_id" : "LAMBORGHINI", "value" : { "model" : 24 } }

{ "_id" : "LAND ROVER", "value" : { "model" : 8 } }

{ "_id" : "LOTUS", "value" : { "model" : 5 } }

{ "_id" : "MASERATI", "value" : { "model" : 6 } }

{ "_id" : "MAZDA", "value" : { "model" : 2 } }

{ "_id" : "MERCEDES-BENZ", "value" : { "model" : 7 } }

has more

Note that not all documents were shown – the shell limits the number to 20 when automatically iterating a cursor. If we want to return the next set of results, there’s the it shortcut

has more

PRIMARY>

PRIMARY> it

{ "_id" : "MINI", "value" : { "model" : 11 } }

{ "_id" : "MITSUBISHI", "value" : { "model" : "COLT" } }

{ "_id" : "NISSAN", "value" : { "model" : 5 } }

{ "_id" : "PERODUA", "value" : { "model" : 51 } }

{ "_id" : "PORSCHE", "value" : { "model" : 10 } }

{ "_id" : "PROTON", "value" : { "model" : 2 } }

{ "_id" : "RENAULT", "value" : { "model" : 4 } }

{ "_id" : "ROLLS-ROYCE", "value" : { "model" : "PHANTOM" } }

{ "_id" : "SSANGYONG", "value" : { "model" : "RODIUS" } }

{ "_id" : "SUZUKI", "value" : { "model" : 2 } }

{ "_id" : "TESLA", "value" : { "model" : "ROADSTER" } }

{ "_id" : "TOYOTA", "value" : { "model" : "PRIUS" } }

{ "_id" : "VOLKSWAGEN", "value" : { "model" : 5 } }

{ "_id" : "VOLVO", "value" : { "model" : 7 } }

To Clean up:

//Drop the output collection
db.myoutput.drop();

See references:

Mongo Docs: http://www.mongodb.org/display/DOCS/Aggregation

MongoDB Aggregation: http://kylebanker.com/blog/2009/11/mongodb-count-group/

Map Reduce: http://www.mongodb.org/display/DOCS/MapReduce

Changing rs config in mongodb

As root: connect to mongo

MongoDB shell version: 2.0.1
connecting to: test
--show rs configuration for the server group PRIMARY> cfg = rs.conf() { "_id" : "repperfmongo", "version" : 3, "members" : [
                {
                        "_id" : 0,
                        "host" : "perf.domain.net:27017",
                        "priority" : 10
                },
                {
                        "_id" : 1,
                        "host" : "perf.domain.net:27017",
                        "priority" : 5
                },
                {
                        "_id" : 2,
                        "host" : "perf.domain.net:27017",
                        "priority" : 0,
                        "slaveDelay" : 3600
                }
        ]
}

For example, to change the slavedelay on the third node (member 2)

-- this makes the change. the property is case sensitive PRIMARY> cfg.members[2].slaveDelay = 0
0
-- this confirms the change PRIMARY> rs.reconfig(cfg) { "ok" : 1 } PRIMARY> cfg = rs.conf() { "_id" : "repperfmongo", "version" : 4, "members" : [
                {
                        "_id" : 0,
                        "host" : "perf.domain.net:27017",
                        "priority" : 10
                },
                {
                        "_id" : 1,
                        "host" : "perf.domain.net:27017",
                        "priority" : 5
                },
                {
                        "_id" : 2,
                        "host" : "perf.domain.net:27017",
                        "priority" : 0
                }
        ]
}

To change the priority of a member:

-- change the priority of member 2 PRIMARY> cfg.members[2].priority = 5
5
-- confirm the change PRIMARY> rs.reconfig(cfg) { "ok" : 1 } -- show new config PRIMARY> cfg = rs.conf() { "_id" : "repperfmongo", "version" : 5, "members" : [
                {
                        "_id" : 0,
                        "host" : "perf.domain.net:27017",
                        "priority" : 10
                },
                {
                        "_id" : 1,
                        "host" : "perf.domain.net:27017",
                        "priority" : 5
                },
                {
                        "_id" : 2,
                        "host" : "perf.domain.net:27017",
                        "priority" : 5
                }
        ]
}