Search results matching tag 'Scripts'

The Windows Azure Software Development Kit (SDK) and the Windows Azure Training Kit (WATK)

BuckWoody — Wed, 12 Sep 2012 13:40:40 GMT

Windows Azure is a platform that allows you to write software, run software, or use software that we've already written. We provide lots of resources to help you do that - many can be found right here in this blog series. There are two primary resources you can use, and it's important to understand what they are and what they do.

The Windows Azure Software Development Kit (SDK)

Actually, this isn't one resource. We have SDK's for multiple development environments, such as Visual Studio and also Eclipse, along with SDK's for iOS, Android and other environments. Windows Azure is a "back end", so almost any technology or front end system can use it to solve a problem.

The SDK's are primarily for development. In the case of Visual Studio, you'll get a runtime environment for Windows Azure which allows you to develop, test and even run code all locally - you do not have to be connected to Windows Azure at all, until you're ready to deploy.

You'll also get a few samples and codeblocks, along with all of the libraries you need to code with Windows Azure in .NET, PHP, Ruby, Java and more.

The SDK is updated frequently, so check this location to find the latest for your environment and language - just click the bar that corresponds to what you want:

http://www.windowsazure.com/en-us/develop/downloads/

The Windows Azure Training Kit (WATK)

Whether you're writing code, using Windows Azure Virtual Machines (VM's) or working with Hadoop, you can use the WATK to get examples, code, PowerShell scripts, PowerPoint decks, training videos and much more. This should be your second download after the SDK. This is all of the training you need to get started, and even beyond. The WATK is updated frequently - and you can find the latest one here:

http://www.windowsazure.com/en-us/develop/net/other-resources/training-kit/

There are many other resources - again, check the http://windowsazure.com site, the community newsletter (which introduces the latest features), and my blog for more.

Reading temporary table from another session

mz1313 — Thu, 01 Mar 2012 17:30:57 GMT

It happens to me at least once a week – I want to check progress of some heavy script that runs in chunks over big dataset and find out that it writes intermediate data to temporary table only. Last time it happened 3 days ago when I wanted to analyze 50GB trace table on my notebook. I wrote a script that was taking 200 thousand rows at a time, parameterizing them and aggregating by different keys – host name, application etc. Usual trace analysis stuff. After an hour I wanted to check the progress but found out that intermediate results are written to temp table and of course I forgot to add debug prints. Took me some thought and ~5 minutes of coding to find the solution. How?

My trace analysis script ran over 200K rows chunks and among other things aggregated them grouping by parameterized query text. One of the measurement columns was “TotalQueries” – counter of rows in a group that could be later used to calculate average values for Reads, Writes, Duration and CPU. Every 200k rows chunk inserted rows into #BA temp table. Last step in a script, when all chunks have already been processed, was aggregation of #BA table’s data into permanent table. So in order to monitor the progress I could SUM all values of “TotalQueries” column in #BA table and compare it to number of rows in my 50GB trace table (which can be easily verified using sp_spaceused).

Of course, I new the temp table name – I wrote the initial script. So first thing was to find its object_id.

SELECT [object_id] FROM tempdb.sys.tables WHERE name LIKE '#BA%'

It brought me negative number: –1546597904 in my current demo. I don’t have access to temp table created by another session. But as admin on my own notebook I sure have access to every page in every database. So next step is to find all pages that belong to #BA table. For this task we have undocumented but widely known (and used) DBCC IND command. Since digging in every page manually and wasting hour on it wasn’t exactly my purpose, I kept DBCC IND output in the table variable and used it later in the script.

DECLARE @BATablePages TABLE        
(        
   PageFID INT, PagePID INT, IAMFID INT, IAMPID INT, ObjectID BIGINT, IndexID INT, PartitionNumber INT,        
   PartitionID BIGINT, iam_chain_type VARCHAR(64), PageType TINYINT, IndexLevel TINYINT, NextPageFID INT,        
   NextPagePID INT, PrevPageFID INT, PrevPagePID INT        
)        
        
INSERT INTO @BATablePages        
EXEC('DBCC IND(2, -1546597904, 1)')

Actually we need only PagePID column and only for leaf level data pages e.g. PageType = 1. Next step is to loop over those pages and insert their content into another table. For viewing page’s data I used another widely known (and undocumented as well) DBCC PAGE command. It is less known that DBCC PAGE can be used along with WITH TABLERESULT suffix. Note: column definitions in the following script are accidental – I just wanted them to be wide enough so that script can complete successfully.

DECLARE @rc INT, @v_PageID INT, @sql NVARCHAR(MAX)        
        
CREATE TABLE #ind( RowNum INT NOT NULL IDENTITY(1,1), PageNum INT NOT NULL )        
CREATE TABLE #page(ParentObject VARCHAR(128), [Object] VARCHAR(512), Field VARCHAR(128), [Value] VARCHAR(8000) )        
        
INSERT INTO #ind( PageNum )        
SELECT PagePID        
FROM @BATablePages        
WHERE PageType = 1        
        
SET @rc = @@ROWCOUNT        
WHILE @rc > 0        
BEGIN        
   SELECT @v_PageID = PageNum FROM #ind WHERE RowNum = @rc        
       
   SET @sql = 'DBCC PAGE(2, 1, ' + CONVERT(VARCHAR(32), @v_PageID) + ', 3) WITH TABLERESULTS'        
        
   INSERT INTO #page(ParentObject, [Object], Field, [Value])        
   EXEC(@sql)        
        
   SET @rc = @rc - 1        
END

So now we have all data pages inside single table but not in a format we’re used to:

But actually, we now have everything we need. The rest is simple:

DELETE FROM #page WHERE Field <> 'TotalQueries'        
                                 
SELECT SUM(CONVERT(INT, [Value])) AS TotalQueries        
FROM #page

SYS2 Scripts Updated – Scripts to monitor database backup, database space usage and memory grants now available

manowar — Sun, 13 Feb 2011 17:30:22 GMT

I’ve just released three new scripts of my “sys2” script collection that can be found on CodePlex:

Project Page: http://sys2dmvs.codeplex.com/

Source Code Download: http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732

The three new scripts are the following

sys2.database_backup_info.sql
sys2.query_memory_grants.sql
sys2.stp_get_databases_space_used_info.sql

Here’s some more details:

database_backup_info

This script has been made to quickly check if and when backup was done. It will report the last full, differential and log backup date and time for each database. Along with these information you’ll also get some additional metadata that shows if a database is a read-only database and its recovery model:

By default it will check only the last seven days, but you can change this value just specifying how many days back you want to check.

To analyze the last seven days, and list only the database with FULL recovery model without a log backup

select * from sys2.databases_backup_info(default)
where recovery_model = 3 and log_backup = 0

To analyze the last fifteen days, and list only the database with FULL recovery model with a differential backup

select * from sys2.databases_backup_info(15)
where recovery_model = 3 and diff_backup = 1

I just love this script, I use it every time I need to check that backups are not too old and that t-log backup are correctly scheduled.

query_memory_grants

This is just a wrapper around sys.dm_exec_query_memory_grants that enriches the default result set with the text of the query for which memory has been granted or is waiting for a memory grant and, optionally, its execution plan

stp_get_databases_space_used_info

This is a stored procedure that list all the available databases and for each one the overall size, the used space within that size, the maximum size it may reach and the auto grow options. This is another script I use every day in order to be able to monitor, track and forecast database space usage.

As usual feedbacks and suggestions are more than welcome!

PowerShell for the DBA: Search the Windows Event Logs for Errors

BuckWoody — Wed, 04 Aug 2010 13:43:30 GMT

This is a very simple script - but it's one I run each morning. It searches the Windows System Event Log for an error condition. You can replace "System" here with "Application" or "Security", or any of the other logs that are created on your Windows Server. This is run at the server, since I have each server check itself and make a file of the results - then I swing by and pick up the files each morning with another script that builds a web page.

# Parse for errors
Get-eventlog System | Where-Object { $_.entryType -eq "error" }

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Viewing how much memory is used by not reused query plan

manowar — Fri, 23 Jul 2010 08:48:54 GMT

One interesting observation that may help developers get convinced that they should parametrize query and that they must check that the ORM they use does it correctly is show how much memory can be wasted by plans that cannot be effectively reused.

The following query can help on this:

with cte as (
    select
        reused = case when usecounts > 1 then 'reused_plan_mb' else 'not_reused_plan_mb' end,
        size_in_bytes,
        cacheobjtype,
        objtype
    from
        sys.dm_exec_cached_plans
), cte2 as
(
    select
        reused,
        objtype,
        cacheobjtype,
        size_in_mb = sum(size_in_bytes / 1024. / 1024.)
    from
        cte
    group by
        reused, cacheobjtype, objtype
), cte3 as
(
    select
        *
    from
        cte2 c
    pivot
        ( sum(size_in_mb) for reused in ([reused_plan_mb], [not_reused_plan_mb])) p
)
select
    objtype, cacheobjtype, [reused_plan_mb] = sum([reused_plan_mb]), [not_reused_plan_mb] = sum([not_reused_plan_mb])
from
    cte3
group by
    objtype, cacheobjtype
with rollup
having
    (objtype is null and cacheobjtype is null) or (objtype is not null and cacheobjtype is not null)

The result is something like this:

as you can see this server (SQL Server 2005 SP2) is using near 1.8GB of memory for Plan Caching and one third is memory that contains plans that are never reused (column usecount = 1 as reported by DMV sys.dm_exec_cached_plans)

Monitoring the result of this query can also show how much impact the usage of the new (SQL Server 2008) “Optimize for ad-hoc workload” option can have on a system.

I’ve also updated my sys2dmv project on CodePlex with this new script:

http://sys2dmvs.codeplex.com/

PowerShell and Extended Properties

BuckWoody — Tue, 20 Jul 2010 12:43:33 GMT

I use Extended Properties on databases and their objects all the time. They are a great way to include information about the object – I use them for versioning the database, detailing what a column is used for and so on. They can be a little tricky to set, but it’s really not bad once you learn how. Ken Simmons, a SQL Server MVP has a fantastic article here that explains more: http://cybersql.blogspot.com/2010/07/extended-property-awesomeness.html

Although it’s not as simple as I would like to set the properties using PowerShell, it’s really trivial to read them. I do this to make system documentation in an HTML page. Open a SQL Server 2008 Powershell provider (sqlps.exe) and CD to any “directory” of a database or tables, views, whatever. Then you can just type this:

DIR | select-object –property name, Extendedproperties

As always, do this on a test system.

Script Disclaimer, for people who need to be told this sort of thing:

Check Script

BuckWoody — Wed, 30 Jun 2010 14:10:04 GMT

Someone contacted me yesterday and said they were getting blocked when they tried to create a FileStream data column type. On investigation, I found they were Mirroring that database – and the two aren’t compatible.

Which got me to thinking – it’s probably a good idea to make a “check script” as you investigate tacking on a new feature, column type and so on to ensure that they are compatible with what you want to do. For instance, in the case of the client that contacted me, I just ran this script as part of the troubleshooting process:

SELECT

FROM

sys.database_mirroring;

You could easily add logic there to test for a partner and report on the process before you tried to implement the change. That’s pretty trivial – but how about creating a “check script” each time you investigate something like this, and put it in your library of scripts? That way you don’t have to recreate the wheel each time you want to make a change in a new system.

Very important – these scripts will change as new releases and service packs come out, so make sure you enter that as comments or even better as a test condition using CASE or other statements.

Create and Track Your Own License Keys with PowerShell

BuckWoody — Thu, 15 Apr 2010 13:10:45 GMT

SQL Server used to have cool little tool that would let you track your licenses. Microsoft didn’t use it to limit your system or anything, it was just a place on the server where you could put that this system used this license key. I miss those days – we don’t track that any more, and I want to make sure I’m up to date on my licensing, so I made my own.

Now, there are a LOT of ways you could do this. You could add an extended property in SQL Server, add a table to a tracking database, use a text file, track it somewhere else, whatever. This is just the route I chose; if you want to use some other method, feel free. Just sharing here.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

And this is REALLY important. I include a disclaimer at the end of my scripts, but in this case you’re modifying your registry, and that could be EXTREMELY dangerous – only do this on a test server – and I’m just showing you how I did mine. It isn’t an endorsement or anything like that, and this is a “Buck Woody” thing, NOT a Microsoft thing. See this link first, and then you can read on.

OK, here’s my script:

# Track your own licenses

# Write a New Key to be the License Location

mkdir HKCU:\SOFTWARE\Buck

# Write the variables - one sets the type, the other sets the number, and the last one holds the key

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseType" -value "Processor"

# Notice the Dword value here - this one is a number so it needs that. Keep this on one line!

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseNumber" -propertytype DWord -value 4

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseKey" -value "ABCD1234"

# Read them all

$LicenseKey = Get-Item HKCU:\Software\Buck

$Licenses = Get-ItemProperty $LicenseKey.PSPath

foreach ($License in $LicenseKey.Property) { $License + "=" + $Licenses.$License }

Script Disclaimer, for people who need to be told this sort of thing:

Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

Adam Machanic — Mon, 05 Apr 2010 02:48:00 GMT

Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script. (Yes, I know the post is dated 2006, but that's because I dropped the ball and failed to back-date the posts when I moved them over here from my prior blog space.)

The impetus for creating this script was (and is) simple: Changing keys can be a painful experience. Sometimes you want to make a clustered key nonclustered, or a nonclustered key clustered. Or maybe you want to add a column to the key. Or remove a column. Or change a data type. Whatever you want to do, you need to remember to drop all of the referencing keys, drop the key you want to change, then put everything back the way it was.

... well, that's not quite true. I was talking with Kimberly Tripp this week and she happened to mention that she was working on a script for the same scenario I was once concerned with. Her script is based on the idea that you don't have to drop the constraints--you can just disable them. I thought back to my own script, and decided to give it a much-needed major update. But I decided that if Kimberly is working on a version based on disabling constraints, I'll leave mine as-is. Then you can use either script--or both--depending on your scenario. If you're just changing a clustering key, Kimberly's may be easier to work with. If you're moving columns in or out of a key, my script will probably get you there a bit faster. More tools means better options! Watch her blog for her version, which should be released shortly.

So what have I updated in my own script?

First of all, the new version is written entirely against the SQL Server 2005+ catalog views. I tried writing the original against INFORMATION_SCHEMA with some goal of cross-compatibility in mind and failed miserably in that pursuit, even then. I've since given up hope of ever writing code that will run (properly) on multiple DBMSs, so it's catalog views all the way from here on out.
The old version only scripts those indexes that are actually declared as "constraints". But a unique index can act as a candidate key just as well as a unique constraint. And with the addition of SQL Server 2005's "included columns" feature, there are lots of great reasons to use a unique index instead of a constraint. So those are now scripted as well.
The old version didn't properly handle system-named constraints. The new version tries to keep things consistent, scripting an explicit name only when one was specified at create time.
The old version didn't handle the various index options such as fill factor, padding, etc, that were around in SQL Server 2000, let alone the newer features added in SQL Server 2005 and 2008. The new version handles all of them: fill factor, padding, ignore duplicate keys, allow row or page locks, and data compression are all supported.
Partitioning? Check--in the new version.
Disabled constraints? The old version may have scripted them in some cases. The new version ignores them, and rightfully so.
The old version didn't properly quote the table names. Oops! Fixed.

There are a few areas that I'm still not touching: XML and FTS indexes, as well as other less-used features that can make the script not work quite as intended. In most cases should you be using these features the drops will simply fail, so you won't wind up with too much of a mess on your hands. But as always, test carefully before using this script. I'll happily fix any bugs you report, but I provide this with absolutely no warranty or guarantee.

To use the script, simply put SSMS into "results in text" mode, specify your target table, and hit F5, CTRL-E, or the Play button. The result should be a nicely-formatted script containing all of the drops and creates, in the correct order. (Foreign keys dropped first, then nonclustered indexes, then the clustered index, if appropriate. Creates are scripted in the reverse order.)

The script is attached to the post. Download, take a look, and let me know if you encounter any issues. Or if you wish to lavish me with praise.

Enjoy!

Note: Don't forget to configure the maximum text size in SSMS before using. The default is 256 characters--not enough for many cases. To configure, use the following sequence:

Tools->Options->Query Results->Results to Text->Maximum number of characters->8192

List SQL Server Instances using the Registry

BuckWoody — Wed, 31 Mar 2010 13:41:24 GMT

I read this interesting article on using PowerShell and the registry, and thought I would modify his information a bit to list the SQL Server Instances on a box. The interesting thing about listing instances this was is that you can touch remote machines, find the instances when they are off and so on. Anyway, here’s the scriptlet I used to find the Instances on my system:

$MachineName = '.'

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $MachineName)

$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )

$regkey.GetValueNames()

You can read more of his article to find out the reason for the remote registry call and so forth – there are also security implications here for being able to read the registry.

Script Disclaimer, for people who need to be told this sort of thing: